I'm having a slight terminology meltdown as I design some classes. In Sql Server 2005, 'schema' refers to a namespace, and an organizational system for database objects. But for relational databases in general, 'schema' means the DDL design of tables, fields, etc. If I'm right about this, it explains a lot of the dissonance when I am trying to read microsoft documentation, and understand the various data access APIs. Can you explain what's going on here, is there really that much difference in the definition of 'schema'?
A:
You are correct. When in SQL 2005/2008, "schema" refers to a namespace, while in a relational database discussion "schema" would refer to the logical structure of the tables, views, procs, functions, etc.
Boo
2009-03-20 17:54:50
This is also the answer, thanks.
P a u l
2009-03-20 20:37:18
+1
A:
Yes, the word "schema" unfortunately has become overloaded among database vendors.
"SQL-99 Complete, Really" says:
An SQL Catalog is a named group of Schemas. ...
An SQL Schema is a named group of SQL-data that is owned by a particular . ... Every Schema Object has a name that must be unique (among Objects of its name class) within the Schema it belongs to. The Schema Object name classes are:
- Base tables and Views
- Domains and UDTs
- Constraints and Assertions
- Character sets
- Collations
- Translations
- Triggers
- SQL-server Modules
- SQL-invoked routines
Oracle uses "schema" and "user" interchangeably, which always makes my eyebrows raise.
MySQL uses SCHEMA as a synonym for DATABASE.
PostgreSQL uses "schema" but uses "database" to refer to what standard SQL calls a "catalog."
Bill Karwin
2009-03-20 18:05:32