Multiple sites in same MySQL database

Question

I'm planning to have 20 or more sites in the same database tables.

Like a structure similar to this: cms_config cms_pages cms_users cms_modules

I have been thinking that each of these tables should have a customer_id column, so I easily could select and call rows that is for the given customer. But is this the best way of doing it when it comes to load time, memory use and all that stuff?

I want a setup that is easy to update and fix, so I thought running seperate databases with same stucture would be a bad idea?

Answer 1

Having everything in the same database, if you are in charge of maintenance, is the easiest path to do updates, maintenance and backups.

Having each database separated increases security and privacy (because each customer database user would have access to every other customer data, by the fact of being able to read/write cms_pages) at the price of (bit of) a higher maintenance cost. Having separated databases also eases performance scaling, where you can move the customer who outgrew expectations to his own server more quickly. Remember updates and backups can be automated, so that isn't that big of a deal.

Performance wise, the customers with lots of data will impact performance of the customers with less data (because the index scan time will grow proportional to the total amount of pages, even if it could be mitigated by the use of multi column indexes.)

So it kind of makes sense to have a separate database per customer and pay the price, unless you are sure these will be small sites with not too much traffic that they are all friends or will never find out their own database username :-).

Answer 2

This is called multi tenant architecture. There are several ways of implementing this. If you invest some time in developing a proper database library you can (ideally) have that rewriting all your queries injecting the tenantId criteria in all queries. This way you know that each tenant only will have access to their data (as long as all queries are passed by the db lib you've created).

Another challenge is to create proper database indexes. You'll often have to create multi column indexes with the customerId / tenantId column + the field(s) you look up on.