DEV Community

Discussion on: What is a multi-tenant system?

Collapse
 
dmfay profile image
Dian Fay • Edited

There's a third model for data segregation: a single schema in a single database, with data identified as belonging to one or another tenant by a key column. Usually the key can be traced back to a table representing all tenants in the system. If you set up foreign keys appropriately, you only need the tenant id in tables immediately related to your tenants, since rows in tables further out are related to rows which are related to tenants. There can be good reasons to add the tenant id field where it's not strictly needed, such as avoiding poorly-performing joins.

The shared-tables approach is by far the simplest and lightest in terms of infrastructure. You don't have to worry about staging new data storage for new tenants, and you don't have to juggle connections to query data for tenant A instead of tenant B: just ensure you're filtering for the correct tenant id. You may not even need that management layer.

It's not all roses, though; you have to be really careful about managing your tenant ids to ensure that nobody sees anyone else's stuff. In certain industries like health or finance, standards or regulations dictate stricter segregation of tenant data. And everyone's data living in the same tables makes backups, restores, and exports an all-or-nothing proposition; I had to develop a tool to let me work with discrete tenant data sets.

Collapse
 
chatelao profile image
chatelao • Edited

This is the golden way to do it. With DB level partitioning and Globally unique table keys you can even move data between tenant groups in different databases. Yes a little bit advanced, but great Stuff :-).

Collapse
 
dmfay profile image
Dian Fay

There's one other caveat I forgot to mention: it's difficult if you let tenants manage installations and/or dictate the pace of upgrades they receive, which if you're not selling service licenses only you probably are. That was the original job I built arachne for, pulling tenant data out of shared storage when they wanted to go on-premise instead. Had I anticipated that scenario I might have done things differently on that one.

Collapse
 
brainwipe profile image
Rob Lang

This is actually the way .net does it by default. I'm surprised the original author didn't include it.