Re: schemas for organizing tables

On Thu, 28 Apr 2011 19:29:11 -0700,
Darren Duncan <darren(at)darrenduncan(dot)net> wrote:

> Seb wrote:
>> A database I'm handling is becoming a bit large'ish (~ 30 tables),
>> and I'd like to break them down into their natural units. Schemas
>> for each of these natural units seems logical, but are they really
>> meant for this? I'm also worried about how this would affect
>> programs like Libreoffice (the sdbc driver in particular)?

> I think that 30 tables is too few to be splitting into schemas based
> just on their number; you should have other reasons for splitting
> them. Also, 30 is quite small, or at least medium-small; some
> databases have hundreds, thousands or tens of thousands of tables.

> Think of a schema like a programming namespace. In a program, you may
> have different libraries or classes that each contain functions and
> such. You would logically group functions together at some times and
> separate them at other times. You can let similar concerns organize
> your schemas, where as schema is like a library as a function is to a
> table ... or some other database object like a stored function.

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?

Cheers,

--
Seb