0

I am currently in the process of designing a multi-business system that involves user management, business details, and order processing. I am contemplating whether to store all business data in a single table or create multiple databases for each business. What would be the most scalable and efficient approach?

I understand that the answer may be "it depends on the number of users, businesses, and orders," but it could be substantial even if things go smoothly, like handling 2 or 3 orders per second. This makes me ponder the scenario where there might be a significant volume of orders from different businesses, and each business will have its own web interface to showcase its products. It resembles a Shopify system, where each business can have its dedicated "web" or "product showcase," and users can make purchases. Importantly, each "business web" is independent of others, sharing only the user table, every business has their own products, orders, categories, etc.

Given that, I would like to ask for your opinion on achieving better performance?

I was searching and asking for the opinions of other people. Now, I want to ask more people to know their opinions and gather different points of view.

3
  • 1
    Your title talks about a database system which would imply a discussion of whether to use MySQL, Oraclw, etc. Your question talks about using a single table or multiple.databases which is not a sensible discussion. Maybe you mean single database vs multiple databases? Feb 4 at 0:07
  • Hi, and welcome to dba.se! This is about (multi-)tenancy - there are various arguments for both approaches - see here and links within. I would tend towards multi-tenancy - you can shut down one client without affecting the others. If you haven't started implementing your project, I would take a serious look at PostgreSQL instead of MySQL. From here, you can have a max of 4,294,950,911 databases - best of luck with that many clients! :-)
    – Vérace
    Feb 5 at 7:33
  • You will likely find out very SOON that you should NEVER co-mingle data or money from two different organizations. The is NO second question to consider. Feb 6 at 21:14

2 Answers 2

1

If I understand your question correctly about how to handle a multi-tenant database, then it does depend, like most things database related. But in particular, it depends on how many tenants you plan to have, what the size of their data will be relative to each other, the level of security and sensitivity of the data, and specifically will you find it more of a pain to manage many databases with things like schema change deployments vs a managing a single database with security and data management, and performance tuning.

Typically it's recommended to decouple each tenant into their own database. This makes managing security better at the database level. It also makes managing the data easier, such as when you need to provide a snapshot of the data to the client via a backup, or even do a backup restore, by not having to dance around the other clients' data within the same tables. And it gives you better control over performance tuning, as depending on the statistical measures of the data for each tenant, different indexes and performance tuning implementations may need to be implemented, which don't apply to other tenants. And if you needed to move one tenant to another server, or drive, it's also that much easier by having their data centralized to a database that only applies to them.

0

whether to store all business data in a single table or create multiple databases for each business

Different servers.

Joining data from different businesses in the different databases of the same server or even in the same table is .... well, good luck with law suits, when one of these businesses will get access to data from all other businesses.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.