Re: What's the point of creating a schema?

On Sat, 2005-09-10 at 13:47 -0400, kynn(at)panix(dot)com wrote:
> Why would one bother to create a schema (with "create schema blah
> blah")? Isn't a database enough to keep tables, etc. separate? I can
> see that schemas *may* be useful for complicated designs having a few
> hundred tables (i.e. one in which name collisions could become a
> common problem), but I can't think of any other situation.

Here's one:

Schemas provide a way to solve a problem in setting up accounting
systems - that of the separation of duties. This is something that
ought to be enforced in any organisation that employs sufficent
accounting staff to make it practicable. The principle is that the
people who deal with sales or purchases should not be the same people as
those who deal with receiving and making payments; when this can be
enforced, opportunities for theft and fraud are significantly reduced.

If an accounting system is implemented in a SQL database and those
accounting functions are organised into different schemas, it is much
simpler to let the database enforce the separation of duties, by
refusing access to information to those who do not need to see or change
it. Of course, the same effects could be attained by setting
permissions on individual tables, but their distribution into schemas
makes a much simpler organisational problem. Use of schemas also helps
the database designer to think in advance about the kind of access that
different groups will require and it provides a framework in which to
set up the system.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html