Mysql – Most efficient table structure for multi-tenant database

database-designmulti-tenantMySQLnormalization

I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations.
We are making a change to the way our site works – from now on Clients will upload / add their own data, which will only be accessible to them. We store the Client data in the client table, each client has an id.
There are 3 simple ways I can think of to design this, but I want the most efficient, as some calls will have to select 10,000+ records for that Client (in the case of a large world map of events, for example). So which if these ways should I use to ensure that all data a Client "owns" is recalled with maximum efficiency?

Many to many relation to each type of entity. –

client.id … | client_id, image_id | image.id …

(and create a many to many table for each of the dozen entities involved). V simple, but could get in a tangle as we add more and more entity types, many of which have relations of their own. New many to many tables will need to be added regularly

Polymorphic association one table to associate client_id with any type of entity. Most efficient in terms of layout but slow to find all entities belonging to one client.

client.id … | client_id, entity_type, entity_id | image.id …

Add owner (client_id) column directly to each entity table

client_id, image_id, image_name etc… (all in the image table, same for each table)

Is there any reason not to add the client ID directly? It would mean storing a few more integers than the other methods but would be extremely fast to select all images 'owned by' a client (could be tens-of-thousands+)

Best Answer

A typical implementation of "multi-tenant" is to have a database for each client.

Each database has the same few dozen tables.
Security (if you care) is somewhat available at the database level.
If your product becomes insanely popular, sharding is relatively easy to add (each server would have several clients).

A downside is the thousands of tables in the whole system.

There would be an extra database for admin stuff -- list of users, their passwords, etc.

If you implement with a single set of tables, then

Client_id would be in many of the tables.
Most indexes would be composite and start with client_id.
Many:many tables may or may not avoid including client_id, depending on whether the Entities have their own id.

Prototype now; analyze; but plan on a major redesign of the schema 6 months from now. Without the overhaul, you will forever be swearing at yourself for making the wrong decision today. And your product will die because it could not flex to meet future needs.

Related Solutions

MySQL data modeling help

I would use separate tables for the project-sponsor and event-sponsor relationships. You are making two many-to-many relationships here, and that just seems like the right way to do it to me.

Can each project/event/org have more than one leader? If not, why not simply put the leader id in the project/event/org table? Otherwise, I would use three tables for the same reason as above: you are describing three many-to-many relationships.

Sql-server – Handling growing number of Tenants in Multi-tenant Database Architecture

At the lower end (500 tenants / 10000 users) this is how I did it. First, you have a "control" database that is global, central and contains all of the information about tenants and users (I really don't think you want to manage these as SQL auth logins). So imagine a database called "Control" with the following tables:

CREATE TABLE dbo.Instances
(
  InstanceID INT PRIMARY KEY,
  Connection VARCHAR(255)
  --, ...
);

INSERT dbo.Instances SELECT 1, 'PROD1\Instance1';
INSERT dbo.Instances SELECT 1, 'PROD2\Instance1';
-- ...

CREATE TABLE dbo.Tenants
(
  TenantID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  InstanceID INT -- Foreign key tells which instance this tenant's DB is on
  --, ...
);

INSERT dbo.Tenants SELECT 1, 'MyTenant', 1;
-- ...

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username VARCHAR(320) NOT NULL UNIQUE,
  PasswordHash VARBINARY(64), -- because you never store plain text, right?
  TenantID INT -- foreign key
  --, ...
);

INSERT dbo.Users SELECT 1, 'foo@bar.com', 0x43..., 1;

In our case when we added a new tenant we would build the database dynamically, but not when the admin user clicked OK in the UI... we had a background job that pulled new databases off a queue every 5 minutes, set model to single_user, and then created each new database serially. We did this to (a) prevent the admin user from waiting for database creation and (b) to avoid two admin users trying to create a database at the same time or otherwise getting denied the ability to lock model (required when creating a new database).

Databases were created with the name scheme Tenant000000xx where xx represented Tenants.TenantID. This made maintenance jobs quite easy, instead of having all kinds of databases named BurgerKing, McDonalds, KFC etc. Not that we were in fast food, just using that as an example.

The reason we didn't pre-allocate thousands of databases as the comment suggested is that our admin users usually had some idea of how big the tenant would become, whether they were high priority, etc. So they had basic choices in the UI that would dictate their initial size and autogrowth settings, which disk subsystem their data/log files would go to, their recovery settings, backup schedule to hinge off of, and even smarts about which instance to deploy the database to in order to best balance usage (though our admins could override this). Once the database is created, the tenant table was updated with the chosen instance, an admin user was created for the tenant, and our admins were e-mailed the credentials to pass along to the new tenant.

If you're using a single point of entry, it is not feasible to allow multiple tenants to have users with the same username. We opted to use e-mail address, which - if all users work for the company and use their corporate e-mail address - should be fine. Though our solution eventually became more complex for two reasons:

We had consultants that worked for more than one of our clients, and needed access to multiple
We had tenants who themselves were actually comprised of multiple tenants

So, we ended up with a TenantUsers table that allowed one user to be associated with multiple tenants.

Initially when a user logs in, the app will know the connection string for the control database only. When a login is successful, it can then build a connection string based on the information it found. E.g.

SELECT i.Connection
  FROM dbo.Instances AS i
  INNER JOIN dbo.Tenants AS t
  ON i.InstanceID = t.InstanceID
  INNER JOIN dbo.TenantUsers AS u
  ON i.TenantID = u.TenantID
  WHERE u.UserID = @UserID;

Now the app could connect to the user's database (each user had a default tenant) or the user could select from any of the tenants they could access. The app would then simply retrieve the new connection string, and redirect to the home page for that tenant.

If you get into this 10MM user area you propose, you'll definitely need this to be balanced better. You may want to federate the application so that they have different points of entry connecting to different control databases. If you give each tenant a subdomain (e.g. TenantName.YourApplicationDomain.com) then you can do this behind the scenes with DNS/routing without interrupting them when you need to scale out further.

There is a lot more to this - like @Darin I am only scratching the surface here. Let me know if you need a non-free consult. :-)

Best Answer

Related Solutions

MySQL data modeling help

Sql-server – Handling growing number of Tenants in Multi-tenant Database Architecture

Related Question