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