3 ms·

In general, "schema" refers to the design of database elements -- table names, tables' columns and their names and types, function names and prototypes, etc. I

In general, "schema" refers to the design of database elements -- table names, tables' columns and their names and types, function names and prototypes, etc. In some RDBMSes "schema" also refers to a namespace that qualifies type, table, view, materialized table, and function names -- this qualifier is optional, so you don't always see it, but all obtjects' fully qualified names include the schema name. This overloading of the word can cause confusion, naturally, but once you understand it it's easy enough to keep it straight. "Let me show you my schema" -> generic sense of the word. "Utilities live in the 'util' schema, while the business logic lives in the 'public' schema" -> the second sense of the word given above.

sixdimensional 7y ago
And the confusion/overloading of the term is even worse than this - in Oracle the concept of "schema" overlaps with "user". In other words, owner of the object and namespace of the object overlaps. It makes sense that the owner of an object is like saying the object belongs to a namespace, but coupling user to that takes getting used to in systems that do that. The ANSI SQL standard (I think part 11) describes the formal definition of schema.
- sbuttgereit 7y ago
  PostgreSQL kinda mimics the oracle way, though it only goes so far. The default definition of PostgreSQL's search_path is: "$user", public So if a schema was named the same as the user, you'd automatically have objects available without qualification. I spent about a fair amount of time working with Oracle and it forces the paradigm of a schema meaning user much more than does PostgreSQL... though with some effort you can make it work somewhat like the logical namespacing capability that PostgreSQL schemas are used for.
- combatentropy 7y ago
  > In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. --- https://www.postgresql.org/docs/current/ddl-schemas.html https://www.postgresql.org/docs/current/ddl-schemas.html

sixdimensional 7y ago

And the confusion/overloading of the term is even worse than this - in Oracle the concept of "schema" overlaps with "user". In other words, owner of the object and namespace of the object overlaps. It makes sense that the owner of an object is like saying the object belongs to a namespace, but coupling user to that takes getting used to in systems that do that. The ANSI SQL standard (I think part 11) describes the formal definition of schema.

sbuttgereit 7y ago
PostgreSQL kinda mimics the oracle way, though it only goes so far. The default definition of PostgreSQL's search_path is: "$user", public So if a schema was named the same as the user, you'd automatically have objects available without qualification. I spent about a fair amount of time working with Oracle and it forces the paradigm of a schema meaning user much more than does PostgreSQL... though with some effort you can make it work somewhat like the logical namespacing capability that PostgreSQL schemas are used for.
combatentropy 7y ago
> In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. --- https://www.postgresql.org/docs/current/ddl-schemas.html https://www.postgresql.org/docs/current/ddl-schemas.html