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?
Advantages. It is easier to carry out backups and recover data with multiple database systems. Because the data is sourced from multiple sources, it doesn't get overwhelming when it is time for backup or recovery.
Closing a database causes it to become unusable until it is opened again. It is recommended that you close any open cursors before closing your database. Active cursors during a database close can cause unexpected results, especially if any of those cursors are writing to the database.
Many modern, data-driven organizations are tasked with managing multiple databases. Often, this is due to complex infrastructures and/or phased digital transformation efforts that see some databases and systems move to the cloud, while legacy systems remain on-premises.
It is all about data, if you have similar data in multiple groups, there is no logic in storing it in multiple table. Always better to store same type of data in a table (entity). For example, when a group having an attribute Mobile_Number , then it there is no logic in storing Mobile_number column in multiple tables.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With