List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:March 5 2008 2:01pm
Subject:Re: what is a schema? what is a database?
View as plain text  
Hi Thufir, all !

Thufir wrote:
> On Tue, 04 Mar 2008 09:48:03 -0600, Paul DuBois wrote:
>>> [[...]]
>> In MySQL, the two are equivalent.  The keyword DATABASE or DATABASES can
>> be replaced with SCHEMA or SCHEMAS wherever it appears.
> Right, but that wasn't exactly what I was asking.  I'm fairly familiar 
> with MySQL but am trying to understand this criticism of it.  Not being 
> familiar with other databases I have no reference point.  What are they 
> getting at?
> Why would you want to do a query of:
> SELECT * FROM database.schema.table;
> Obviously, this is non-sense in MySQL, where database <==> schema.

AFAIK, this all goes back to an ANSI standard for SQL in the mid-80s.

That standard had a "CREATE SCHEMA" command, and it served to introduce 
multiple name spaces for table and view names. All tables and views were 
created within a "schema". I do not know whether that version defined 
some cross-schema access to tables and views, but I assume it did.
AFAIR, no product (at least back then) really implemented it, that whole 
  concept was more theory than practice.

OTOH, ISTR this version of the standard did not have the concept of a 
"user" or a "CREATE USER" command, so there were products that used the 
concept of a "user" (who then had his own name space for tables and 
views) to implement their equivalent of "schema".

This is an area where systems differ.

As far as administration is concerned, this should not matter too much, 
because here you have differences anyway.

As far as you look at application code, you "only" have to care about 
cases where one application accesses tables from multiple name spaces. 
AFAIK, all systems support a syntax "<name space>.<local identifier>", 
and for this it should not matter whether the name space is that of a 
user, a "schema", or a "database".
(I do not claim having done a complete research, so maybe there are 
systems which differ in this regard.)

I have not heard of a three level naming scheme yet.


Joerg Bruehe, Senior Production Engineer

what is a schema? what is a database?Thufir2 Mar
  • Re: what is a schema? what is a database?bmurphy2 Mar
  • Re: what is a schema? what is a database?Martin Gainty3 Mar
  • Re: what is a schema? what is a database?Thufir4 Mar
    • Re: what is a schema? what is a database?Paul DuBois4 Mar
  • Re: what is a schema? what is a database?Thufir5 Mar
    • Re: what is a schema? what is a database?Joerg Bruehe5 Mar
  • Re: what is a schema? what is a database?Thufir5 Mar