CODEWITHSUNDEEP
sql-serverentity-frameworkazureasp.net-web-apiazure-sql-database
Reynaldi
Reynaldi

Reputation: 1165

Composite primary key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP.NET Web Api, Entity Framework and SQL Server / Azure database.

This app will be used by 1000-5000 customers. All the tables will have a TenantId (Guid) column. Right now, I use single column primary key which is Id (Guid).

The problem with that is, I have to check if the data supplied by the user is from / for the right tenant. E.g I have a sales order table which has a CustomerId column. Everytime user post/update sales order, I have to check if the CustomerId is from the same tenant.

It gets worse because each tenant might have several outlets. Then I have to check TenantId and OutletId. It's really a maintenance nightmare and bad for performance.

I'm thinking to add TenantId as primary key along with Id. And possibly add OutletId too. So primary key in sales order table will have multiple columns, Id, TenantId, OutletId.

What is the downside of this approach? Would the performance hurt badly using composite key? Does the composite key order matter? Are there better solution for my problem?

Upvotes: 0

Views: 1596

Answers (1)

Stephen Reindl
Stephen Reindl

Reputation: 5819

  1. If you want to use a composite key please use always (if possible) all elements of the key to avoid index range scans

  2. The order of elements is important. If you want to retrieve for example all data related to a single tenant (see pt. 1) you should use TenantId as the first element (see Why does primary key order matter?)

  3. For Azure SQL you should also consider concepts like Elastic Scale or partitioning for legacy systems which could improve performance.

  4. Do you really need a guid for the TenantId? If you have a limited amount of customers (<= 5000 is not that much) you could also use a int (or even int64) type which would allow easier partitioning (see pt. 3)... I do like guids a lot, but for ease of maintenance and if I do not need the big range for data I try to avoid them (see this article for performance). If you need the tenant id in advance, you can use sequences (a new feature for SQL Azure V12): 

    create sequence CustomerSequence as int
     start with 0
     increment by 1;
select next value for CustomerSequence;

EDIT: Added information about sequences in SQL Azure

Upvotes: 2

Related Questions