Thread: why use SCHEMA? any real-world examples?

I just noticed PostgreSQL's schemas for my first time.
(http://www.postgresql.org/docs/current/static/ddl-schemas.html)

I Googled around, but couldn't find any articles describing WHY or
WHEN to use schemas in database design.

Since the manual says HOW, could anyone here who has used schemas take
a minute to describe to a newbie like me why you did?   What benefits
did they offer you?   Any drawbacks?

Thanks for your time.

- Miles

Hi!

It's really just a convenience-thing to organize your data in a more intuitive way. We're running several online
magazines,each of those with a sort of "entity-database", but each with their own articles. So we've just put the
entity-datain the public schema, whereas the magazine-specific data is going in their own schemata. That way we can
simplyuse the very same queries for all of our magazines' applications, just by implementing the magazine-schema as a
variablewhich is set at query-runtime.  

Kind regards

   Markus

> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von
> Miles Keaton
> Gesendet: Donnerstag, 25. November 2004 06:13
> An: pgsql-general@postgresql.org
> Betreff: [GENERAL] why use SCHEMA? any real-world examples?
>
> I just noticed PostgreSQL's schemas for my first time.
> (http://www.postgresql.org/docs/current/static/ddl-schemas.html)
>
> I Googled around, but couldn't find any articles describing
> WHY or WHEN to use schemas in database design.
>
> Since the manual says HOW, could anyone here who has used schemas take
> a minute to describe to a newbie like me why you did?   What benefits
> did they offer you?   Any drawbacks?
>
> Thanks for your time.
>
> - Miles
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Miles Keaton wrote:
> Since the manual says HOW, could anyone here who has used schemas take
> a minute to describe to a newbie like me why you did?   What benefits
> did they offer you?   Any drawbacks?

Well - it's a namespace feature, so at its simplest it lets you have two
objects with the same name. It also lets you have permission control
over them, and provides a convenient way to group items together. For
example, I usually have a "util" schema where I keep utility
functions/views for dba use rather than general users.

For a longer example, you might have a database with two sets of users -
sales and accounts. They both need to print reports, but not the same
set of reports. So - you create a "reports" table with an access code of
S=sales, A=accounts, *=everyone.

You wrap this with a view "my_reports" where you supply your user-type
(S/A) and get a list of reports you can access. However, your
application needs to supply the user-type and if someone can inject the
right SQL into your connection, they can gain access to any report.

So - you create 3 schemas: core, sales, accounts. You put the "reports"
table into core and two views into "sales" and "accounts", both named
"my_reports" and rewritten appropriately. You deny access to "reports"
directly, and make sure your application sets its "search_path" to
contain the relevant sales/accounts schema. Then "SELECT * FROM
my_reports" will show only those reports your login allows.

HTH
--
   Richard Huxton
   Archonet Ltd

> > Since the manual says HOW, could anyone here who has used schemas take
> > a minute to describe to a newbie like me why you did?   What benefits
> > did they offer you?   Any drawbacks?

I suspect the consumer of this feature for whom it would make the biggest
difference would be shrinkwrapped packages. For example, if you have a
database and want to add on a logging package, a billing package, and a CMS
system and you want to store the data in your existing database so you can
integrate them all closely together, then it would be nice to at least keep
the database tables in separate namespaces.

--
greg