Pros and Cons needed: Central or separate databases

spakkle

Dear All
I need some advice here, and i know without a doubt this is the best place to ask.

I am currently programming an application which is going to be online, I want to know what are the pros and cons of using one centralised MySQL database for all the applications (all will generally be the same) or whether to use separate databases, Hypothetically there could be 2000 sites which will need the database.

I am worried that by storing all data for each site in the same database would be disasterous should that database fail, go wrong or be compromised (as in all sites would go down etc), I am wondering if the size of the database could cause issues?? (as it could get rather large is multiple sites are using it could this slow things down) and if most of the sites needs an additonal field in the database, there will be hundreds of additional fields in the database's tables which may only be used by one site.

Personally, i think having each site use separate databases would mean a little more work (which i dont mind) but would mean at least they are separate entities, so if one should fail or be compromised only one will go down and only one will need fixing. If separate, the database size would not be an issue at all (if it could be an issue that is) and each database can be slightly tailored if need be, so there is not hundreds of fields there.

all pros and cons welcomes, what do you guys and gals think......

Arc

Well, if you are planning on having each database be unique (i.e different filds in each database) which it sounds like you are, then you should definately go with seperate databases.

Remember though, upkeeping 2000 databases is a monumental task and not something I would do.

Are you storing a lot of data on these databases, such as adding row after row after row of new data all the time (someting like this messageboard) or is it mostly just rows that get updated (such as changing which image/text shows up on the home page) ?

if you are constantly adding new rows then speed couple be an issue if you use a single database after a long period of time. However, if you are deleting old data occsionaly it shouldn't be a problem at all.

Personaly, I would go with one database and make sure I backed it up daily. If something goes wrong you can just restore the database with the back up.

spakkle

Hi Arc
thanks for your reply. The databases will generally be the same (client data and records), but there may be some with different fields (as in say website designers will have fields for them, but some will also do domain and hosting so some will need additional fields for those)

the main thing it is used for is adding new data, not much of it will be updating existing records. I think that records will want to be kept almost permanently so there will not be much deleting either.

There wont be much upkeeping really, as its the owner of each database's job to do that, not ours.

I am mainly worried about all sites going down if something should go wrong, or if it is compromised then they can distroy everything and every site, and you say that speed could be a problem, could this be a major problem or does it depend on the size of the database

spakkle

The pros and cons i know of so far:

CENTRAL DATABASE PROS:
all in one place
easier to amend if needed.

CONS:
lose everything if it goes wrong
anyone has access to the entire database should it be compromised.
Could get rather large in size.

SEPARATE DATABASES:
PROS
separate entity with separate credentials,
if something goes wrong or is compromised only this one goes down.
smaller in size

Cons:
harder to update if a field needs adding to all of the databases.

feel free to add to this list....

MarkR

I would tend to say have as few databases as possible - if you can do it centrally, so much the better.

Manageability is always an advantages - large applications are difficult to manage, having fewer components means easier management.

Of course it's possible that it will need to be refactored in the future, especially if it becomes very large, but you should cross that bridge then.

Remember the principle, "Do the simplest thing that could possibly work" ?

Mark