Single DB or multiple DB (for multiple users in a single aplication)

Question

I´m new on php/mysql, and i´m codding a simple CMS. But in this case i will host multiple companies (each company with their multiple users), that pays a fee to use the system.

So... My question is about how to organize the Data Base... Talking about security, management and performance, i just want to know the opinion of ou guys of wich of these cases is the best:

1. Host all companies on a single DB and they get a company id to match with the users.

2. Each company have a separated DB that holds the users in there (and dont need the companies id anymore).

I would start the development following the first situation... But than i thought if i have some hacker attack / sql injection, every client would be harmed. Having separated DBs, the damage will get only one client. So maybe the 2nd situation could be better in terms of security. But could not say the same about management and performance.

So, based on your experience, any help or tip would be great!

Thanks in advance, and sorry about my poor english.

Answer 1

If you plan to have this database hosted in a cloud environment such as Azure databases where resources are (relatively) cheap, clients are running the same code base, the database schema is the same (obviously), and there is the possibility of sharing some data between the companies then a multi-tenant database may be the way to go. For anything else you, you will probably be creating a lot of extra work going with a multi-tenant database.

Keep in mind that if you go the separate databases route, trying to migrate to a multi-tenant cloud solution later on is a HUGE task. I only mention this because all I've been hearing for the past few years around the IT water coolers is "Cloud! Cloud! Cloud!".

Answer 2

I would go for seperate DBs. But not only for hacking.

Scalability: Lets say you have a server that handles 10 websites, but 1 of those websites in growing fast in requests, content, etc. Your server is having a hard time to host all of them.

With seperate DB's it is a piece of cake to spread over multiple servers. With a single one you would have to upgrade you current DB or cluster it, but that is sometimes not possible with the hosting company or very expensive.

Performance: You they are all on 1 DB and data of multiple users is in 1 table, locks might slow down other users.

Large tables, mean large indices, large lookups, etc. So splitting to diffrent DB's would actualy speed that up.

You would have to deal with extra memory and CPU overhead per DB but they normaly do not have an amazingly large impact.

And yes, management for multiple DBs is more work, but having proper update scripts and keeping a good eye on the versions of the DB schema will reduce your management concerns a lot.

Update: also see this article. http://msdn.microsoft.com/en-us/library/aa479086.aspx

Answer 3

You haven't provided any details, but generally speaking, I would opt for separate databases.

Using an autonomous database for every client allows a finer degree of control, as it would be possible to manage/backup/trash/etc. them individually, without affecting the others. It would also require less grooming, as data is easier to be distinguished, and one database cannot break the others.

Not to mention it would make the development process easier -- note that separate databases mean that you don't have to always verify the "owner" of the rows.

Answer 4

Separate DBs has many advantages including performance, security, scalability, mobility, etc. There is more risk less reward trying to pack everything into 1 database especially when you are talking about separate companies data.