Multiple databases vs a single database

Question

I'm looking at an existing site and they are using separate databases. The databases seem to be setup in the following manner:

• general types (user_type, language, age, etc)
• member data (registration information & login)
• site configuration

Personally, I would put everything into 1 database. To me this would make it easier than almost always having to put the database name in front of every table.

I can see from a security perspective ensuring that people cannot access the wrong data by accident, but on this site there are going to be admins (about 10% of the users on the site) that will need to access all the databases, doing cross database searches.

What could be the reasons for doing separate databases? (The site is in PHP & MySQL.)

Edit: The names of the db are:

• sitename (the actual site name) (general types)
• member (member data)
• siteconfig (site configuration)

Answer 1

Purely speculation on what was in the minds of the creators:

Maybe a difference in the volitilaty of the data, so that there could be a different backup/replication strategy for the different physical dbs?

Maybe an idea that say, "general types" could be shared across multiple applications but the "site configuration", for example, would be specific to just one application?

Maybe an idea that the different databases could be put on different pieces of hardware that have different performance characteristics, like their RAID configuration. Data that is read a lot but not updated versus data that is updated a lot.

Again, pure speculation...

@Darryl - my answer is more archeology than technology. I'm not saying I buy any of it. I'm just trying to step into the mindset of the ancestors...

Answer 2

@Corey Trager: Good idea, but you can of course both specify which tables to backup and also, how much difference would it make to only select from some while always updating other tables?

Answer 3

Also pure speculation: Perhaps it was an architectural decision to support separation of concerns.

Answer 4

I can't think of a reason to create multiple databases for the information you listed, especially if that data needs to be cross-linked. It sounds more like someone didn't know how to use permissions so instead of granting access to tables they just created new databases. Just an opinion, but cutting things down to just one database would probably be an improvement.

Answer 5

If there were a good reason for breaking up a database into a bunch of smaller ones like this, the databases would probably have names like "hadronsupercolliderrawdata" and "googlebackup_2008". Names like "generaltypes" and "memberdata" suggest they were just overwhelmed by stupidity.