One database vs many databases [closed]

Question

I know the question has been asked many times but I'd like an answer for my case.

I'm working on a web application which will allow my clients to manage their clients, encashments, invoices, reservations, a website, and many other things. I'm using MySQL and one database with about 30 tables.

I'd like my solution to be able to handle around 100,000 clients or more. The needs of my clients will be very different. From 100 inserts per year for one, to 1000 inserts per day for another.

For now I'm using one database (but I'm still in development) where each table has an account field. I've created a model layer to access the data which automatically append the account to each query (WHERE guid = 1 become WHERE account = X AND guid = 1). This work very well and is really easy to maintain, but I'm worried about the fact of mixing the data of my clients. Note that I'm using an incremental id instead of a GUID.

My question is, should I continue to do things like this or should I create one database per client?

Answer 1

You're looking at a multi-tenant database. Multi-tenant solutions range from one database per client (shared nothing) to one row per client (shared everything).

"Shared nothing" is the most expensive per client. Large numbers of clients imply large numbers of servers. Client disaster recovery is simple and straightforward. "Shared nothing" reduces the possibility of accidentally exposing client data to almost zero.

"Shared everything" is the least expensive per client. Every table has a column that identifies which client a row belongs to. Client disaster recovery is very complicated; you have to restore individual rows in every table. "Shared everything" is the architecture most likely to accidentally expose client data.

Microsoft has a good article on multi-tenant architecture. Their terminology is

• separate database (shared nothing)
• separate schema
• shared schema (shared everything)