Seb wrote:
Thanks for these thoughts. Perhaps I can describe a cartoon of this
database to explain what I'm trying to accomplish.
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?
If you mean using just 2 schemas for those 2 distinct tasks, then yes, that may
be a reasonable organization strategy. However, it is important to keep
yourself flexible to a degree such as to change your mind on this decision or
reorganize now and then. Similar to how you should expect you may refactor or
reorganize your program now and then, though you could design now for how you
reasonably expect to be for awhile. This all said, I could go either way. Your
proposal is a more reasonable purpose for using 2 schemas, but at the same time,
because you only have 30 tables for now, it may not hurt to keep them together
in 1 schema anyway.
Okay, bottom line, for simplicity, stick to a single schema for everything until
you can point to a clear benefit for splitting. If you're on the fence and
could go either way with no clear benefit of one way over the other, then just
use 1 schema for everything by default as that is a simpler approach.
As for the possibility of supporting another DBMS later without schema support,
well ... Postgres is free and is generally superior to all other free DBMSs, and
probably most of the DBMSs you would have to choose from also support schemas
anyway ... or if you need to use the others you could fake it with common
prefixes for your table names.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
