How to correctly implement the database structure for a multi-user platform?

Question

Database

The task is to implement a platform for creating online stores - something similar to a website builder. Will it be correct to store the data for all stores in one database, or for each of them to deploy a separate database? Who developed such applications, maybe there is a more logical approach to the task?

Answer 1

Constructors have a typical workload - about zero for most sites and a small percentage of active ones. Wrapping each one individually in your own database is very tedious in support. We lived up to 1000 created sites (which is not so much if not to say "in general crumbs") - you have 1000 databases, which is already quite dofiga. Rolling out a new version of the application for sale is already an adventure.
If you want SaaS - i.e. end users have no direct access to the database - then the classic sharding scheme will be much easier to support. In addition, you have data from different clients obviously in the subject area does not intersect in any way.
- site_id is entered in client-specific tables and is included in unique keys and other happiness of integrity restrictions (for postgresql you can additionally fasten row level security and the database will additionally keep an eye on it)
- your billing and user management are located separately, where, among other things, you write a correspondence table, which site_id is located on which physical host and in which database (plus a list of ro-replicas)
- you can cache the site’s correspondence to the database in some redis and go from one single pool of application servers, which is much easier to scale the application (if the application server is considered stateless , which again is much easier to maintain).
As a result, the scheme allows you to work with a meaningful number of large databases (starting with one and introducing new ones as needed), inactive sites to migrate to archive databases, hot ones from expensive clients - even to allocate to a separate infrastructure. By the way, the focus on separating the VIP client into a separate infrastructure allows you to give this client both direct access to the database if necessary and the ability to create custom functionality just for him.

Answer 2

If this is a group of stores under the same brand selling different categories of goods (one is cosmetics, another is household appliances, the third is books, etc.), then everything is in the same database, users are in the public scheme, each store is in a separate scheme. As the load grows, it may be necessary to move schemas to separate databases/servers.
If these are different stores for different owners - of course, everything is in separate databases and, possibly, even on different servers.

Answer 3

Since you ask such a question, it means that the task is clearly too tough for you.
Throw this dead idea.