SaaS. One database per user or to each his own?

Question

MySQL

Hello.
I'll bring up an old topic.
You need to make a SaaS service. The application got ready, but for one user (MySQL). In principle, it can be modified for several users, so there is a choice:
1 - use one database
2 - use a separate database for each user
I don’t see it possible to divide by tables, because it’s a lot of work and it’s easier to write an application from scratch.
The main problem is that the base will grow.
For the first option, you can use sharding partitioning.
The second option is more like it because if there are problems with some database, other users will be able to continue working calmly.
Someone writes about possible problems with updating the database for all users (10,000 users = 10,000 databases), but I see no problem writing a script for this, and monitoring can, in principle, be automated.
In addition, you need to add subscription and billing.
For the first option, you need to refine the application (also make a client account and an admin account).
For the second option, the application will have to be changed only to determine which base/address to use.
for example, name1.domain.com will use the name1 base, and name2.domain.com will use the name2 base on another server.
The address of the database servers and passwords should be stored in sqlite (so as not to depend on mysql).
And for the second option, write a separate admin panel where the client will pay for the subscription, etc. and where the administrator can manage these clients and databases. Update sqlite database data (sqlite allows you to work with multiple connections).
Please share your experience, who faced similar tasks and how did you solve it?

Answer 1

And how will you make a sample from 10,000 bases? I have a hard time imagining that option.

Answer 2

Идите по пути наименьшего сопротивления. Сначала используйте отдельную базу для каждого пользователя. А когда будут те самые 10000, тогда и будете думать о оптимизации. Я не силен в базе, даже сказал бы слаб. Но что-то мне кажется, что когда баз станет 1000 - уже будет подтормаживать. Ведь выбрать базу из 1000/10000 - не запись из одной таблицы. <- лишь мысли в слух.
Да и не вижу какой-то проблемы во время оптимизации и переноса. 10000 баз слить скриптом в одну при оптимизации - вопрос минуты.

Answer 3

Так сходу лично мне ближе вариант "один пользователь - одна база".
Как минимум это не вызовет вопросом "почему у меня ID заказа/товара увеличивается не на 1 а произвольно".
Вариант "однако одна база на всех пользователей" может вызвать вопросы с масштабированием в будущем.
Есть компромиссный вариант - одна база на N пользователей - решает вопрос с количеством БД и масштабированием.
Ссылки по теме:
https://habrahabr.ru/post/110979/
https://habrahabr.ru/company/microsoft/blog/145027/
SaaS: одна БД на клиента, или общая база данных?

Answer 4

The user will both read and write to the database. The database will be used by the user in its entirety, so I don’t see anything bold in trying to ensure uninterrupted work for the user.
I did not say that I have problems with remaking the application into a multi-user one. I'm just trying to choose the best solution so as not to waste time now and not regret it later.
That's why I'm asking, perhaps someone had experience and will speak out regarding 1 user = 1 base.