Should I split the data between multiple databases or keep them in a single one?

Question

Hi,

I'm creating a multi-user/company web application in php & mysql. I'm interested to know what the best practice is with regards to structuring my database(s).

There will be hundreds of companies and thousands of users of this web app so this needs to be robust. Each company won't be able to see other companies data, just their own. We will be storing mainly text data and will probably only be a few MB per company.

Currently the database contains 14 tables (for one sample company).

Is it better to put the data for all companies and their users in a single database and create a unique companyID for each one.

or.

Is it better to put each company's data in its own database and create a new database and table set for each new company that I add.

What are the pluses and minuses to each approach.

Thanks,

Stephen

Answer 1

If a single web app is being used by all the different companies, unless you have a very specific need or reason to use separate databases (it doesn't sound like you do), then you should definitely use a single database.

Your application will be responsible for only showing the correct information to the correct authenticated users.

Answer 2

I agree with philfreo. When you have multiple databases you will spend much more time maintaining it.

Answer 3

A single database is the relational way. One aspect from this perspective is that databases gather statistics about database usage and make heavy use of this. If you split things up you will be shooting yourself in the foot as the statistics will be fragmented.

Answer 4

Multiple databases would be a nightmare to maintain. For each new company you'd have to create and administer each one. If you make a change to one schema, you'll have to do it to your 14+.

Thousands of users and thousands of apps shouldn't pose a problem at all as long as you're using something that is a real database and not Access or something silly like that.