Re: Big Tables vs. many Tables vs. many Databases

olbertz(dot)dirk(at)gmx(dot)de ("Dirk Olbertz") wrote:
> Hi there,
>
> I'm currently about to redesign a database which you could compare with a
> database for managing a library. Now this solution will not only manage one
> library, but 100 to 500 of them. Currently, eg. all the data about the
> inventory (books) is held in one table for all the libraries.
>
> Is it useful to spread this to one table for each library, by eg. giving it
> an id as a postfix?
>
> For one library, we currently need about 150 tables, so that would easily
> increase a lot if there would be a set of this tables for each library. On
> the other hand, there are only a very few tables (2-5), which are used by
> all libraries. All the rest does not interact with each other - and don't
> think about exchanging books betweens libs, as the library is only an
> example...
>
> One other solution would be to make one database for each library. What do
> you think of that? Does PostgreSQL has any problems with that much tables?
> Would it better to spread the data by databases?

The big challenge that you'll face (and this would be true with _any_
DBMS) if you separate things into separate databases is that the
proliferation of connections will get pretty frightful, and there will
be no option of having your application use connection pooling as the
connections won't be the same.

It would seem to me that having a "branch" field to identify which
library a particular entry about would be The Way to split off the
data.

That in effect means having one database with 150 tables.

If you _really_ want to separate things into having 150 tables for
each library, then the appropriate approach would likely be to set up
a schema for each library. That allows using one database, and not
utterly preventing joining data from the separate libraries in cases
where that would be useful...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/emacs.html
"The beginning of wisdom for a [software engineer] is to recognize the
difference between getting a program to work, and getting it right."
-- M A Jackson, 1975