Composite Primary Key in multi-tenant SQL Server database
27
votes
1
answer
7846
views
I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have
TenantId
(Guid / UNIQUEIDENTIFIER
) field. Right now, I use single field Primary Key which is Id (Guid). But by using just the Id field, I have to check if the data supplied by the user is from / for the right tenant. For example, I have a SalesOrder
table which has a CustomerId
field. Every time users post/update a 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
to the Primary Key along with Id
. And possibly add OutletId
, too. So the Primary Key in the SalesOrder
table will be: Id
, TenantId
, and OutletId
. What is the downside of this approach? Would the performance hurt badly using a composite key? Does the composite key order matter? Are there better solutions for my problem?
Asked by Reynaldi
(371 rep)
Apr 17, 2015, 08:12 AM
Last activity: Dec 12, 2019, 07:14 PM
Last activity: Dec 12, 2019, 07:14 PM