Re: schemas for organizing tables - Mailing list pgsql-general

From fork
Subject Re: schemas for organizing tables
Date
Msg-id loom.20110429T175800-513@post.gmane.org
Whole thread Raw
In response to schemas for organizing tables  (Seb <spluque@gmail.com>)
List pgsql-general
Seb <spluque <at> gmail.com> writes:

> The database stores information related to biological research.  The
> bulk of the tables describe things like individual ID, morphometrics,
> and behavioural data on all the individuals in several studies.
> However, there are a few tables that do not relate to the research
> itself (the main use of the DB), but to logistics.  For example, a group
> of tables store information on purchases and inventory of material
> required for the overall project.  These tables would never (or almost)
> be queried together with the others.
>
> So typically we have two types of uses: research and
> preparation/logistics for the project.  We wouldn't want to even see the
> logistcs tables for research work, whereas we would like to see only
> these ones for preparation/planning.  As the project and number of
> tables grow, we could see a similar divergence within the group of
> tables related to research.  Are these good reasons for separating these
> units into schemas?

One thing that splitting into schemas would give you is the ability to play
games with "SEARCH_PATH".  Users who handle logistics would have their
search_path only set to "LOGISTICS,public" and would need to fully qualify a
research table to even see it (like "select * from research.foo"); any table
creation would also happen in the schema RESEARCH.  Running an unadorned "\d"
from the command line will only show tables in the search path, which might be
useful too.  Vice versa for RESEARCH.

That being said, ..., less complexity is always better, so unless this is really
compelling because people are complaining, I wouldn't bother.  My rule is always
"pull" technology within an organization, never "push"; on your own experimental
box, though, it is well worth hacking for understanding.

>
> Cheers,
>




pgsql-general by date:

Previous
From: Dennis Grimm
Date:
Subject: Re: load sql from the file in postgresql
Next
From: Mark
Date:
Subject: Re: GIN index not used