Choosing a Database system

Question

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.

Answer 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.

Answer 2

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.