Thread: Database Design Question
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. Thank you!
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.
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
On Wed, 2011-02-02 at 11:08 -0800, John R Pierce wrote: > On 02/02/11 10:32 AM, Carlos Mennens wrote: > 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. Forget separate databases. Use separate users with schemas. JD > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On 02/02/11 11:24 AM, Joshua D. Drake wrote: > Forget separate databases. Use separate users with schemas. for canned applications like mediawiki and phpbb? not sure they support that.
>> Forget separate databases. Use separate users with schemas. > for canned applications like mediawiki and phpbb? not sure they support > that. Mediawiki does -- I'm doing just that. It's been liberating learning how PostgreSQL deals with schemas (and applying that knowledge). -- Gary Chambers
carlos.mennens@gmail.com (Carlos Mennens) writes: > 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. > > Thank you! I think it's likely that these would properly have separate databases, as... - There isn't *that* much data that is likely to be shared between these applications, so it probably doesn't add a lot of value to force them together. - If you integrate the databases together, then any maintenance on "the database" represents an outage for *ALL* those systems, whereas if they're separate, there's at least the possibility of outages being independent. You'll have to think about the expected kinds of failure cases to determine in which direction to go. -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www3.sympatico.ca/cbbrowne/rdbms.html Make sure your code does nothing gracefully.
On Wed, Feb 02, 2011 at 11:44:51AM -0800, John R Pierce wrote: > On 02/02/11 11:24 AM, Joshua D. Drake wrote: >> Forget separate databases. Use separate users with schemas. > > for canned applications like mediawiki and phpbb? not sure they > support that. > If they use different users you can easily do it by setting the default search path per user. ALTER USER phpbb SET search_path='phpbbschema'; As long as the apps don't play with the search path themselves it should be fine. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
Attachment
> 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. > > Thank you! As a general rule, whenever you have applications that don't share anything should not be in the same database. This is because of flexibility, it is a lot easier to move an application to another server in the future, if you decide to break them up. If the only thing that is shared is a users table, I would move the users to ldap. Sim
Thanks for all the suggestions and everyone appears to agree that if the applications don't need to share data, then I should split them up into separate database and nothing more. I appreciate your input and explanations as well. -Carlos