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

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: kynn(at)panix(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: What's the point of creating a schema?
Date: 2005-09-10 23:38:11
Message-ID: 1126395491.2295.21.camel@linda
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Luciano 2005-09-11 04:46:35 I can't put the password in anyone program
Previous Message Kretschmer Andreas 2005-09-10 20:32:36 Re: What's the point of creating a schema?