Many tables for many users?

Question

I have a web application that can be seen in many ways as a multi-tenant environment. By this, I mean that each user of the application gets their own "normal" environment without absolutely any interaction between these users.

So far, I have created a web application as a single-user environment. In other words, I actually did nothing to support multiple users, but only worked on the functionality that I want from the application. Here is my problem ... What is the best way to create a multi-user environment:

• All users point to the same "main" backend. In other words, I create logic to separate users through the corresponding SQL queries (for example, select * from the table where user = '123' and attribute = '456').
• Each user points to a unique table space that is created separately when they join the system. In this case, I would simply create ALL the corresponding SQL tables for each user with some kind of suffix for the user. (for example, now the query will look like "select * from table_ where attribute = '456").

In short, this is the difference between "select * from table where USER =" and "select * from table_USER".

Answer 1

Creating tables is dynamically rather messy and confusing. In addition, if you have many users, this will be complete chaos if you have many volumes, especially if you need to change something in n tables instead of a separate table.

-> Use one table and add some user_id column. With appropriate indexes, it will be as fast or even faster than individual tables.

Answer 2

The first option is better.

Shared tables should contain normalized data, you should not duplicate the same table.

Also, the first option is safer, since you do not need to provide the ability to create or drop real tables into the program

Answer 3

I would say that your choice depends. You really have three options:

One database to all their rules ... (your choice 1)

Of course, adding a TenantId column TenantId it easier to add new tenants (users), but there are some disadvantages:

• You have to be careful that each query filter from TenantId. It would be very easy to accidentally forget TenantId in the right place and return the other tenant details.
• All top parent tables must include TenantId. This is not only your master data, but also all the parent data related to the tenant.
• Tenants cannot be in different versions of the scheme at different times. Suppose, for example, that you are making some changes to the data schema in version 1.1 of your application. If all tenants are in the same database, each should be updated at the same time, whether you want them or not. In addition, if you use a single database, you are almost obliged to use one site, because you want the site and the scheme to remain in sync. If you do this, you cannot, for example, instruct someone to upgrade to get a new feature. Functions should be built as plugins, not versions depending on the version, which may not be bad, but from the very beginning it should be an informed decision.
• This can be a daunting task for sharing tenant data if they want to have a copy of their data or want to post their own data or if you want to move them to another database server. Since all resources are divided, you may encounter a situation where one tenant chews resources through reports or traffic, so you want to transfer them to your own database server (and sell them this advantage). In addition, I came across situations where tenants want to get a copy of their data, which they can download on their own. If all the data is in the same database, this can be a daunting task.

If you are going to sell to corporate clients, I would not go this way. However, if you plan to add thousands and thousands of end users as tenants, where you do not need to provide their data, then using one database is probably the right approach.

Tenant segment according to the scheme (for example, Tenant1.Table1, Tenant1.Table2 ... Tenant2.Table1, Tenant2.Table2 ...) (I consider your choice 2)

IMO, this is a more complex version of the simple use of individual databases. The advantage is that maintaining one database is a bit simpler, but besides this, the same problems arise as using separate databases.

Tenant database segment

For corporate clients, I found that in the end it turns out to be the easiest. This eliminates the possibility that the tenant will see incorrect data (if the connection string is incorrect). This allows corporations to host their own system. This allows tenants to be on different versions if each tenant has different virtual applications. This makes it easy to allocate resources, back up and restore. This is only (but not insignificant) minus - this is the time cost of installation (and, consequently, financial costs). It can be painful to add databases when you get a new client. Technically, it can be automated, but it is still a pain.

So, ultimately it depends on your target customer. If they are standard users, I would go with the approach “One database to manage everything” and make sure that you do a lot of code reviews and automatic testing. If these are corporate clients, especially large corporate clients, then I would consider separate databases for each tenant.

Answer 4

The only way to split tables for each tenant makes sense if you have a separate database for each tenant, in which case the tables will still have the same name.

Otherwise, use a single table for each property and filter them by tenant ID.

Answer 5

If you are running SQL Server, I recommend using single tables for all tenants, not granting access to the underlying tables no matter what the application uses, and restricting access to the built-in table functions. This is similar to parameterized views and means that no one who has access to them can ever get a set for more than one tenant at a time (so it doesn’t accidentally join another product catalog):

 CREATE TABLE [dbo].[mt]( [ID] [int] IDENTITY(1,1) NOT NULL, [TenantID] [int] NOT NULL, [BusinessKey] [varchar](50) NOT NULL, CONSTRAINT [PK_mt] PRIMARY KEY CLUSTERED ( [ID] ASC ), CONSTRAINT [IX_mt] UNIQUE NONCLUSTERED ( [TenantID] ASC, [BusinessKey] ASC )) CREATE FUNCTION f_mt ( @TenantID AS INT ) RETURNS TABLE AS RETURN ( SELECT * FROM mt WHERE TenantID = @TenantID ) 

If you have a TenantID stored somewhere in the connection (using CONTEXT_INFO ()), it is also possible to have simple views that wrap this:

 CREATE VIEW vw_mt AS SELECT * FROM f_mt(CONTEXT_INFO()) 

It all depends on how much abstraction you want to use.