Multiple databases, or always limit with query

Question

I have a question regarding databases and performances, so let me explain the situation.

The application - to be build - has the following set-up: A group, with under that group, users. Data / file-locations, (which is used to search through), estimated that one group can easily reach one million "search" terms.

Now, groups can never look at each other's data, and users can only look at the data which belongs to their group.

The only thing they should have in common is, some place to send error logs to (maybe, not even necessary).

Now in this situation, would you create a new database per group, or always limit your search results with a query, which will take someones user-group-id into account?

Now my idea was to just create a new Database, because you do not need to limit your query, every single time and it will keep the results to search through lower (?) but is that really necessary or is, even on over a million records, a "where groupid = 1" fast enough to not notice a decrease in performance.

Answer 1

This is the regular multi-tenant SaaS Architecture problem, which has been discussed at length, and the solution always varies according to your own situation. Here is one example of this discussion that I will just link to instead of copy-paste since all of it is worth a read: Multi-tenant PHP SaaS - Separate DB's for each client, or group them?

In addition to that I would like to add some more high level considerations:

• Are there any legal requirements regarding the storage of your user's data? Some businesses operate in a regulatory environment where they are not allowed to store their data in a shared environment, quite common in the financial and medical industries.
• Will you offer the same security (login method, data storage encryption), backup/restore service, geolocation redundancy and up-time guarantee to all users?
• Are there any users who are willing to pay extra to have their data stored in a separate environment?
• Are there any users who will potentially have requirements that are not compatible with the standard product that you will be offering? If so will you try to accommodate them? Note that occasionally there is some big customer that comes along and offers a lot of cash for a special treatment.
• What is a separate environment? Is it a separate database, a separate virtual machine, a separate physical machine, a machine managed by the customer?
• What parts of your application is part of each environment (hardware configuration, network config, database, source code, binaries, encryption certificates, etc)?
• Will there be some heavy users that may produce loads on your application that will negatively impact the performance for the smaller users?
• If you go for all users in one environment then is there a possibility that you in the future will create a separate environment for some customer? If so this will impact where you put shared data, eg configuration data like tax rates, and exchange rate data, etc.

I hope this helps.

Answer 2

Performance isn't really your problem, maintaining and data security is. If you have a lot of databases, you will have more to maintain. Not only backups but connection strings, patches, schema updates on release and so on. Multiple databases also suggests that you will have multiple PHP sites too. That will gradually get more expensive as the number of groups grows.

If you have one database then you need to ensure that every query contains the group id before it can run.

Database tables can be very, very large if you choose your indexes and constraints carefully. If you are performing joins against very large tables then it will be slow but a simple lookup, where you have an index on the group column should be fast enough.

If you were to share a single database, would you ever move a group out of it? If that's a possibility then split the databases now. If you are going to have one PHP site then I would recommend a single database with a group column.