Composite Primary Key in Multitent database RRS feed

  • General discussion

  • Hi ,

    What is the Pro and Cons of creating Composite primary key in case we have multitenant database? Specially performance wise and Lock wise.

     Thanks in advance..

    Regards Vikas Pathak

    Tuesday, September 15, 2015 3:08 PM

All replies

  • Can you post some examples of your database and tables?

    Do you mean you have one database with multiple tenants? (then consider using schemas to separate and secure them)

    Or do you mean you have an instance with one database per tenant and all the databases  have a common design?

    In general, with PKs, you want them narrow and ever increasing (or decreasing, but that's not so common).  If you are designing a data warehouse (dimensional model) you probably want surrogate keys that are ints or bigints.  If you are designing an OLTP database, a composite key can be just fine if it's not too wide.

    With a little more information, it should be easier to see what direction you should go

    Thursday, July 21, 2016 7:47 PM
  • Composite primary keys have been around for a long time. There are not huge problems with them unless you have too many columns in them (like 3 or 4 varchar(20) columns) and you carry those down to child tables are foreign keys.

    Like anything else in software development, work has to be done to maintain the indexes. Having a multi tenant database for multiple clients is one frequently used scenario.

    Also, remember, you can still have a Identity or Sequence number (unique) as a clustered index to be used in non-clustered indexes for performance.

    Performance is the main problem with multi-column primary keys. Again, a good developer or DBA will be able to manage this successfully.

    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Friday, July 22, 2016 3:54 PM
  • If you mean "adding the TenantID as the leading column in every primary key", then

    The Pros are

    1) The rows for each tenant are kept together, giving you better locality and the ability to scan per-tenant

    2) The need to add TenantID predicates to every query helps enforce Tenant isolation

    The Cons are

    1) You have to add TenantID to every query

    Perf-wise there really are no significant Cons, other than those _inherent_ to multi-tenant databases. 

    Big Picture, most of my customers are looking for ways to move _away_ from multi-tenant databases, and use a database-per-tenant architecture. 

    The benefits of database-per-tenant are many:

    1) Each tenant gets their own execution plans, not a one-size-fits-all plan

    2) Tenant-level backup and restore

    3) Easy mobility of tenant data

    4) Guaranteed tenant data isolation

    5) Tenant-isolated reporting

    6) Tenant-isolated administration and security

    7) The ability to patch and version per-tenant

    8) The ability to perform maintenance per-tenant

    The cons of database-per-tenant are mostly just the additional administration, and the need to automate thoroughly all routine operations.


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 22, 2016 4:13 PM