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

Hi,

Citing Miles Keaton <mileskeaton(at)gmail(dot)com>:
> 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.

When your data are too similar to be split into two databases but
at the same time too different to fit into common tables, a schema
comes in handy to keep your db tidy.

> 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?

We had agricultural experiments running here at our institute. We had
both field experiments outside and pot experiments in the greenhouse.
The data collected was mostly the same for both sets of experiments
(plant nutrient content, growth parameters like shoot length...), but
the number of samples taken per measured parameter was quite different
(e.g. for the pot experiments, nutrient data was available from each
and every plant, whereas we took samples in the field only from a subset
of plants). So both sets of experiments did not fit into one clean
normalized, relational model. On the other hand, it was quite desirable
to have all the data in one db, to be able to run queries across both
datasets at the same time from one connection. Schemas provided a nice
way to keep this all clean and simple.

> What benefits
> did they offer you?

Clean, logical organization of one projects data in one db.

> Any drawbacks?

If you have tables with the same name in several schemas, only
the ones, which are in the first schema in the search path are
shown on \dt from psql. Not a major problem, but keep this in mind
when designing the db.

Regards,
Daniel