I am currently working on an application that has potential for growth. The entire application runs through one domain, and subdomains are provided to clients, which means that all this, of course, passes through a common code base.
What I'm struggling with is database design. I am not sure if it would be better to have a column in each table with the identifier of the client or create a new set of tables (in the same database) or create a complete new database for each client.
The good thing about the “flag” in the database identifying the client ID is that everything is in one place. The falls are obvious - the tables can (will) become huge, and the service can be a complete nightmare. If growth happens, splitting it into multiple servers will be a huge pain.
The best part about creating new tables is easy to do, and also makes the tables pretty small. And since customer data does not need interaction, there is no problem. But then again, service can be a problem (although I have a migration library that will make updates on the fly for each client, so that doesn't really matter). Another problem is that I do not know how many tables can be in one database. Does anyone know what the limit is and what will be the performance issues?
The good idea of creating a new database for each client is that when I need to scale, I can, very well. There are several sites that use this design (wordpress.com, etc.). It has been shown to be effective, but also has some disadvantages.
, , () .