Should I use multiple databases?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Should I use multiple databases?

Post by nutkenz »

I'm going to gather data which I will need in multiple websites hosted on the same server. The data is very general; f.e. country codes and their names in different languages. The data will only be updated by use of a script which requests the data from other servers (which might be down or slow sometimes which is why I need to keep it locally).

I'm now doubting which structure to use in order to have as little difficulties as possible maintaining the data. Some options I'm considering are:

- Adding a seperate database and connecting to it when needed.
Advantages: Data is always up-to-date and doesn't need to be synchronized
Disadvantages: Multiple database connections needed for a single website

- Adding a seperate database and exporting it regularly
Advantages: Single connection per website
Disadvantages: Requires manual work

- Some kind of API which returns the needed data in XML format
Advantages: Single connection per website, Data is always up-to-date and doesn't need to be synchronized
Disadvantages: Requires a lot of effort to build

- Having tables in all databases and have these updated by a seperate script
Advantages: Only the script requires multiple DB connections
Disadvantages: Table structure modifications will have to be made twice

What would you do?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Separate database, same user login (per website if need be) which only has selection rights.
Advantages: simple query using the same connection they already use - pretty nice.
Disadvantages: does require referencing a database in the query - minor.

I think it's win-win.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

I've been always using one database. I don't know when I should use another. When would be a good time to use more then one database?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

JellyFish wrote:I've been always using one database. I don't know when I should use another. When would be a good time to use more then one database?
Under typical circumstances, there's little reason to ever use more than one. As developers we are likely to use several, however. Mostly due to having code and data in various development stages that need proof-of-concept testing before plowing further.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

The general rule that I follow is: if there is a relationship among tables, they all should be in one database, unless logistics, such as distributed data, is a factor. If the data is totally unrelated and not used in the same application, use separate databases (makes it easier to migrate, etc.).
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

califdon summed up my thinking. Also bear in mind the necessity of maintenance if changes are made to basic database design. Do changes need to be replicated across the multiple databases. If so can you automate this so you don't miss any. It can be really annoying when databases become out of sync because you missed an update on one.
Post Reply