views:

965

answers:

5

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)
+8  A: 

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...

Corey Trager
99% of archeology is sifting through the mounds of crap that human beings leave behind, so your analogy is excellent.
MusiGenesis
Here! What a good name/title for those who have to maintain software (especially a poorly documented one): Software Archeologist! I like it! And I like the answer, of course!
Yarik
A: 

@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?

Darryl Hein
+1  A: 

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

Daniel M
Not up on the termonology...can you explain concerns?
Darryl Hein
Correct me if I'm wrong here, but in general it means you separate the design and work, and hand it off to a different developer, to find the best solution for that problem instead of a generic solution for everything.
Robert Gould
+1  A: 

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.

Lando
It's amazing what ignorance can produce. I work with a database that has no primary keys at all, a situation which came about because the original developers didn't know how to create views.
MusiGenesis
A: 

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.

MusiGenesis