What is the best way to organize the service architecture?

Question

Software design

There are 2 questions on service architecture.
1 - a dedicated (pseudo-dedicated) service for some customers who want to work in a subdomain (allegedly their own separate installation)
2 - load sharing depending on the country / company / number of employees
As for the first question - you need to make a separate service instance for the customer, or emulate its singling out, it all depends on the advantages / safety of the approaches, but I’ll take a look at those that I guessed (suggested):
1.1 - use in all tables related to the customer’s resources, in addition to the customer’s identifier, also the serial number of the line specifically for this customer (the method, in my opinion, is the most crutch and not adapted to the load)
1.2 - use for each customer its own table with an identifier as a postfix for each table (the method is less burdensome, but there is no confidence in the safety of such a number of tables)
1.3 - use within the same service for each customer its own database specifically for information related to this customer (as an example, 20 tables are used in the service, 10 of them are used to store the customer's data, so 10 tables are used in the service database, and each customer has its own database on 10 tables). There is a feeling that this will work faster than previous solutions, but such bulkiness is very scary, there is a feeling of being under an avalanche.
1.4 - use within the same server with the main domain a subdomain for a customer that has a set of tables similar to the main one in its database, only differs in that it is located in a third-level domain and only the customer's employees can work in it. It seems to me the most correct solution, but it confuses the possibility of running into the limit of server resources and not understanding what to do next.
1.5 - use a separate virtual machine for each subdomain. There are no resource issues in this solution, but there is a problem with automatically raising an instance and keeping all instances up to date.
Perhaps there are other solutions, I will be glad to hear them.
As for the second question, there is not a small chance in the previous question to follow the path 1.5, but everything else is planned to connect all instances with common functionality (at least correspondence). And this will entail the need to associate user messages from one virtual machine with users of another. In this case, I see only 3 solutions so far 2.2 - a centralized server is started as a router, which should send, depending on some factor, the client to another server where the message associated with it is stored (it can be the author or recipient). This option is more plausible, but the implementation of such a router and how quickly it will work is not clear.
2.1 - a centralized server is started that keeps all correspondence (considering how often people like to write, it seems to me that one table with correspondence will start to slow down very soon)
2.3 - when sent, the message is stored by the sender, and a copy is also stored by the recipient. It seems to me that this is the easiest solution, but again it is embarrassing that at some point within the same server there will be an unbearably large number of these messages, which will affect performance.

Answer 1

According to the first: the most secure is still separate virtual machines.
However, if the question is only in the database, subject to the normal architecture of the application itself and the database, let's say PostgreSQL / Oracle Then I would dwell on the schemes. Moreover, each subdomain operates from its own user/scheme with clearly defined grants.
Only systems completely isolated from the user come into contact with the control circuit.
Initial copying of databases for posgre, say: from a simple dump of the main schema / create table like to create table of type / inherits
On the second point: with proper implementation, nothing will slow down, in any case, I have 250 million messages with 2 thousand active users, they did not slow down at all. Here you need to think more about the implementations of the messages themselves. If we say we can delete our message from the correspondence, then its copy is not needed, if we can’t, then a sign like
-message_id
-message_text
-message_theme (if necessary)
-parent_id (for the response hierarchy, if necessary),
-folder_id (if daddies are needed)
- author_id
-author_name
-recipient_id
-recipient_name
-keeper_id
Solves all problems. Two instances are created for each message and, accordingly, if keeper_id==author_id then this is outgoing, if keeper_id==recipient_id is incoming. We cache the names of the sender / recipient in the corresponding fields so as not to perform an extra join for each sneeze and also so as not to lose the addressee if you want to wipe the user from the database (which in itself is a bad idea). We decide whether we need themes, folders, etc. and compile indexes for our requests - everything: joy and happiness, butterflies fly.

Answer 2

IMHO, if you have such questions, then it's time to open a vacancy for a software architect, this is the right decision!

Answer 3

The question is very simple - when can the data of one customer be used by another? Not? Then the fewer dependencies, the better.
Separate databases for individual customers - this will simplify their internal structure and solve issues with access sharing. As for load balancing, this is solved by using virtual machines or containers.
So you will have complete isolation, and pictures on the go, and other joys.
Tools for controlling VM instances, clustering and balancing the load on the cluster are available, free of charge, and very digestible.
My personal example - Xen - Pacemaker/Heartbeat - OCFS2

Answer 4

Under 1.5 you can use docker. Or Puppet/Chef style systems. Much depends on exactly what kind of services you are doing.
At the expense of 2.3 and 1.5 - be prepared for the difficulties of support. A VERY big responsibility falls on the update rolling subsystem.