One database for each client or all clients in one database. which should i use for online application?

Question

So, I have this application, which will have several modules, from project management to accounting modules. The question is, should I have one database for each client (company) or one database that contains everything?

1) which one will be better? 2) it will be much more difficult to manage multiple databases or it is manageable.
3) We are going to have the same application for all users, which means that the same scheme will be used regardless of the number of databases.
4) some clients will have a lot of things (accountants, for example, can have up to 2 million rows added per year in one table), while others will use much less data.

What do you think I should use?

Answer 1

1) The presence of separate databases allows you to simplify the load distribution on multiple hosts, it raises the roof in different ways; disk, memory, lock, processor, backup time, etc. If you are serious about putting millions of rows in mysql, this is certainly a good idea with separate databases (not just schemas) and even separate instances, so that resource-intensive clients will not impose downtime on less resource-intensive ones.

2) It will be exactly N times harder to manage, where N is the number of databases: o) This is an additional cost that you must compare with the cost of using only one db / circuit and instead manage the client separation in the code, Also, it’s much it’s more difficult to manage if you need to call support at your hosting company or even with your local grumpy dba, instead of just running a neat script from your console every time you need to update the schema or create a new database.

Some databases and retention systems retain multi-tenant support, Oracle has this, and support is starting to appear in Hibernate 4.

Despite the fact that many arguments point to separate databases, as a rule, only one database can be used.

Answer 2

In theory, multiple databases would be better for performance. That is, if you can place them on separate disk controllers. But in reality they will most likely be on the same drive, so there probably will not be an increase in performance. In addition, additional disks are better used as additional elements of RAID arrays than as additional separate logical disks onto which you can unload data.

In terms of maintenance, multiple databases will be a nightmare. Each ALTERMATION to the database should be done N times, where N is the number of clients. Of course, you will never do it manually, so you will always have to do it programmatically, and pretty soon you will begin to evaluate how easy it would be if you could just make the same changes with a few clicks on the management console instead, you need to write code to do them for you every time.

Answer 3

security will be a real bear if you do not use separate databases, regardless of any other problems. You should be extremely careful with coding to make sure that one company does not see (or modify or delete) the data of other users.