Re: Database Design Question - Mailing list pgsql-general

The main concern to consider is whether there are any shared relationships
that the different projects all have (e.g., common logon users).  Since you
cannot query across different databases if there is shared information then
a single database would be preferred.  I think the concept you want to
consider further is "Schemas".  You can get the same kind of separation that
you would want with multiple databases with the possibility to have a
"global" schema that holds data common to multiple projects.

Also, I would suggest managing permissions by "group" roles and strictly
assigning "user/logon" roles to those group roles.

If, from an application standpoint, the structure does not matter then
consider the maintenance aspects of such a design.  The advantage of
multiple databases is that you can easily put each database onto its own
machine and individual applications can be brought offline without bringing
down all the applications.  Your admin tool will also have references to
each of the separate databases instead of a single database with multiple
schemas.  If you end up using maintenance functions and/or views they will
probably need to be installed and configured in each database.  At the same
time it becomes easier to look at the maintenance logs when each application
is independent (of course this depends on the tool and how schemas are
handled).

Dave


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, February 02, 2011 2:09 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database Design Question

On 02/02/11 10:32 AM, Carlos Mennens wrote:
> I was sitting down thinking the other day about when is it good to
> generate a new database or just use an existing one. For example, lets
> say my company name is called 'databasedummy.org' and I have a
> database called 'dbdummy'. Now I need PostgreSQL to manage several
> applications for my company:
>
> - webmail
> - software
> - mediawiki
> - phpbb forum
>
> Now what I've been doing is just creating multiple tables in the
> 'dbdummy' database but each table is owned by different users
> depending on their role. Is this bad? Should I be creating new
> databases for each application above rather than one single company
> database?
>
> Just trying to understand good DBA design practice. This is obviously
> a very general question but any feedback on what good or bad issues
> would come from me dumping all my tables for applications in one
> database or spread out across multiple databases on PostgreSQL.

I would create a seperate database for each thing that has nothing to do
with the other things.    I doubt mediawiki and phpbb will ever share
any data, they are totally different applications, each is a self
contained world.  ditto your webmail.   the other item there,
'software', well, I have no idea what that means specifically.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general