sql-server

Which of definitions of database schema is correct?


These definitions from answers at https://stackoverflow.com/questions/2674222/what-is-purpose-of-database-schema are different.

Which is correct?

A SQL schema is container of objects. For example, you may have a large enterprise application and then it's a good practice to use different schemas for different purposes (e.g. put HR related tables into HR schema, accounting related tables into Accounting schema and so on). A schema can be owned by any user, and the ownership is transferable. [https://stackoverflow.com/a/2674259, partly from http://msdn.microsoft.com/en-us/library/ms190387.aspx]

The database schema is the definition that describes the entire configuration of the database, including all of its tables, relations, index, etc. [http://en.wikipedia.org/wiki/Database_schema]


Solution

  • Both are correct. "Schema" has multiple meanings. Your first meaning is the kind of schema you get with CREATE SCHEMA -- an object container. Your second meaning is the general meaning of "database schema" that's independent of SQL Server. Usually it's clear from context which meaning is intended -- "database schema" is typically singular, while "schemas" within a single database refer to the containers. So, if you wanted to be particularly confusing, you could talk about a database schema2 that has multiple schemas1 in it.