0

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?

Reynaldi
  • 1,125
  • 2
  • 19
  • 41
  • Just to be clear: any table can have **exactly one** primary key - never more than one. That primary key *might* consist of multiple columns - but it's still just **one** primary key – marc_s Apr 17 '15 at 08:32
  • Could you please explain the question in better way? What is the relation between tenant, customer, outlet, sales, etc. It is little difficult to understand. – CleanBold Apr 17 '15 at 10:52
  • I'm developing a point-of-sale application. Tenant is the customer who buys my product. Each tenant has one or more shops/outlets. Each tenant sells things to his customers. Hope that clears my question – Reynaldi Apr 17 '15 at 12:43
  • 1
    @marc_s noted. I've updated my question. sorry for my english – Reynaldi Apr 17 '15 at 12:47
  • Reynaldi você usa GUID na chave primária turnê? O que o desempenho? Eu estou tenho uma tabela com 20 millions linha – Rod Jun 12 '15 at 00:44

1 Answers1

1

  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

Community
  • 1
  • 1
Stephen Reindl
  • 5,659
  • 2
  • 34
  • 38
  • Yes, i think its better to use integer for the tenantId field for performance. I use guid because it is more convenient. Because when i add tenant, i also need to add other data to other tables. With guid i can instantly get the value, while in identity field i have to wait after the changes is saved. Do you have an idea how to handle that? – Reynaldi Apr 20 '15 at 05:44
  • For the last update of SQL Azure you can use sequences: `create sequence CustomerSequence as int start with 0 increment by 1` and `select next value for CustomerSequence` – Stephen Reindl Apr 21 '15 at 08:57