Multi-tenant: Does every table need a tenant ID

pietb · #1 (**permalink**) 01-05-11, 06:27

After reading articles on multi-tenancy, specifically shared db/schema designs, it is recommended that a tenant ID be included in every table. Is this really true? For example, if I wanted to offer forums in my (hypothetical) SaaS app to my tenants, I might have something that looks like the following tables and fields:

• Categories (Fields: Category ID, Tenant ID, Name)
• Forums (Fields: Forum ID, Category ID, Tenant ID, Name)
• Posts (Fields: Post ID, Forum ID, User ID, Tenant ID, Title, Text)

Here is my question: why would I need the Tenant ID in the Posts table? Couldn’t I just do a join on the Tenant, Users, and TenantXrefUsers tables? These latter tables might look like:

• Tenant (Fields: Tenant ID, Name)
• Users (Fields: User ID, First Name , Last Name)
• TenantXrefUsers (Fields: Tenant ID, User ID)

Is it because performing joins is costly?

Thanks in advance for your helping me with my curiosity.

r937 · #2 (**permalink**) 01-05-11, 06:36

what's a tenant?

pietb · #3 (**permalink**) 01-05-11, 06:41

A tenant would be a company using the application. Sorry if that was unclear.

futurity · #4 (**permalink**) 01-05-11, 14:44

It depends on your business rules, what you're trying to accomplishing, and how much of this you want to enforce in your database.

For example, if you wish to enforce that users can only post in forums to which their company belongs, it's pretty easy to do with a referential integrity constraint:

Code:

create table post (
    ...
    tenant_id int not null,
    forum_id int not null,
    user_id int not null,
    foreign key (tenant_id, forum_id) references forum (tenant_id, forum_id),
    foreign key (tenant_id, user_id) references tenant_user (tenant_id, user_id),
    ...
)

Without carrying the tenant id, you must enforce this in your application logic.

dav1mo · #5 (**permalink**) 01-05-11, 20:41

also, with having tenant id in every table, you could access the tables via views. the views would be defined as select * from table where tenant id = @tenant. You would set the @tenant, based on the customer that signed in.
Dave

blindman · #6 (**permalink**) 01-10-11, 12:21

You can access data through views regardless of whether you include TenantID in every table. That's what views are for, as a matter of fact. And the SQL example you gave would work as well on a table as on a view.
Also, "SELECT *..." is not the best-practices method for creating views. Enumerate your fields.

pietb · #7 (**permalink**) 01-11-11, 15:59

All...thanks for your replies. From reading your comments, I gather that the ultimate answer is: yes, a tenant_id should probably be used in every table - but it could be left out, depending on business rules and what I'm trying to accomplish.

Thanks for the help.

Internet Services

Web Development

Digital Marketing

Consumer Tech