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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
