Re: schemas for organizing tables

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