MySQL Question

[jgbishop]

I have a number of collections of data that I would like to display on a website. All of the collections are mutually exclusive; their contents completely unrelated.

My question is this: should I store all of the collections in a single database (using different tables)? Or should I use one database for each collection?

There isn't a ton of data in each collection (i.e. the databases will be fairly small), so the former option seems more attractive. Also, the database connection process in MySQL (via PHP) seems expensive, also supporting the former option. But, because the data is mutually exclusive, it doesn't make much sense to have it all lumped together in one place.

Does anyone have any suggestion as to how I can best handle this?

 

[cker]

Well, you could rationalize that the unifying trait of all this unconnected data is that it's used by the same website

Sounds like you've decided the solution - save the connection expense and load the stuff into a single DB. If you want, you can have a standard prefix for your tables based on the purpose. For instance, I have a single database on a web hosting account named 'dstore1' for my personal stuff. A wordpress installation is there - all those tables are prefixed with 'wp_'. All my SiteBar stuff is prefixed with 'sb_'. For yet another app, a forum, I used 'bb_' for one installation, 'nuke_' for another.

The prefixed name would also hopefully discourage any other developers involved from tying together unrelated tables... My rule is whatever is before the first underscore designates the table purpose. Whatever mnemonic or system works for you, works for you. It's mainly personal preference... I tend not to share databases with other users, so I only can blame myself if I pollute other tables.

Of course, you *can* put the stuff in various databases. Depending on the frequency of connection, and the lifetime of the connections, and the number of users, the connection cost may not be a significant factor. What do you think, based on the way the databases will be used?

 

[jgbishop]

The number of connections and users is likely to be fairly low, and the connection lifetime is likely to be short (connections will most likely only occur when building the HTML). I like your idea of table prefixes ... that's something I hadn't thought of!

 

[kamper]

It's really a matter of what's most convenient/safe for you. As for connection times, are persistent connections an option?

One potential, if unlikely, reason to split them up is performance. It won't benefit you now, but in the future you could physically seperate the databases if you needed to. Of course, if you maintained the logical seperation, you could always do it later if you needed to. If you had rigid security roles to enforce, keeping them seperate can also help there.

I'd keep them together if for no other reason than ease of maintenance, since your requirements don't seem to be too strenuous otherwise.

 

[ArmchairAthlete]

If things get big eventually it's going to be sorta rediculous with everything in one DB. If you can group the tables into a few separate groups that make sense I say go for that over putting them all in one...

And yea, if they get big you could map the different DBs to different drives right? I'm no dba lol.

 

[skace]

If the data is completely unrelated, store in seperate databases, this way 1 database may grow faster than the rest or take on more users and need it's own server. At which point it would be very easy to make the transition.

 

[Armitage]

I'd keep it in one database and not deal with seperate connections for each data type. Any performance considerations will be per-table anyway, and if you ever want/need to move some of the data to another database it's as simple as moving the files from one directory to the other.

 

[Future Shock]

This isn't really much of a question - feel free to put it all in one database, as it is used by the same application. Commercial applications like SAP, Siebel, etc. have literally THOUSANDS of tables in one database, most of which have little relation to each other in a given context. And the overhead for seperate databases is a pain, especially when coding...

Future Shock