Re: When do you separate databases?

On Tue, 6 Feb 2001, Conrad Schuler wrote:

> I am managing a database design project.
>
> It will database our entire website including:
>
> images, articles, bibliography, ecommerce, membership in website, site
> statistics, etc.
>
> The 'lumper' team member says "let's make it all one giant database with
> dozens and dozens of tables".
>
> The 'splitter' team member says "let's make it many separate databases each
> having fewer tables".
>
> What are the advantages and disadvantages to 'lumping' all tables into one
> database vs. 'splitting' into multiple databases with fewer tables?

If the data is all inter-related, it should all go into one database or
schema.  In PostgreSQL, you can't access tables across multiple schemas
unless you use multiple database handles in an application written in a
language that can do multiple handles.  Unless you have a *very* good
reason to split the data across multiple schemas (like perhaps one schema
needs to be secured behind a firewall because it has very sensitive data,
but then you would be putting it on a separate machine anyway...), you
should keep all of your data in one schema.  It isn't going to impact
performance, and with PostgreSQL, it will require a lot of extra
programming to handle multiple databases anyway.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
It's amazing how much better you feel once you've given up hope.