7

I am looking at multi-tenancy database schema design for an SaaS concept. It will be ASP.NET MVC -> EF, but that isn't so important.

Below you can see an example database schema (the Tenant being the Company). The CompanyId is replicated throughout the schema and the primary key has been placed on both the natural key, plus the tenant Id.

Plugging this schema into the Entity Framework gives the following errors when I add the tables into the Entity Model file (Model1.edmx):

  • The relationship 'FK_Order_Customer' uses the set of foreign keys '{CustomerId, CompanyId}' that are partially contained in the set of primary keys '{OrderId, CompanyId}' of the table 'Order'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_OrderLine_Customer' uses the set of foreign keys '{CustomerId, CompanyId}' that are partially contained in the set of primary keys '{OrderLineId, CompanyId}' of the table 'OrderLine'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_OrderLine_Order' uses the set of foreign keys '{OrderId, CompanyId}' that are partially contained in the set of primary keys '{OrderLineId, CompanyId}' of the table 'OrderLine'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_Order_Customer' uses the set of foreign keys '{CustomerId, CompanyId}' that are partially contained in the set of primary keys '{OrderId, CompanyId}' of the table 'Order'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_OrderLine_Customer' uses the set of foreign keys '{CustomerId, CompanyId}' that are partially contained in the set of primary keys '{OrderLineId, CompanyId}' of the table 'OrderLine'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_OrderLine_Order' uses the set of foreign keys '{OrderId, CompanyId}' that are partially contained in the set of primary keys '{OrderLineId, CompanyId}' of the table 'OrderLine'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.
  • The relationship 'FK_OrderLine_Product' uses the set of foreign keys '{ProductId, CompanyId}' that are partially contained in the set of primary keys '{OrderLineId, CompanyId}' of the table 'OrderLine'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.

The question is in two parts:

  1. Is my database design incorrect? Should I refrain from these compound primary keys? I'm questioning my sanity regarding the fundamental schema design (frazzled brain syndrome). Please feel free to suggest the 'idealized' schema.
  2. Alternatively, if the database design is correct, then is EF unable to match the keys because it perceives these foreign keys as a potential mis-configured 1:1 relationships (incorrectly)? In which case, is this an EF bug and how can I work around it?

Multi-tenancy database schema

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Rebecca
  • 13,914
  • 10
  • 95
  • 136
  • If I remove the composite primary keys and just use the natural keys (ProductId, OrderId, CustomerId, OrderLineId) the EF error goes away. However, I'm not sure if that is just shoveling the crap under the carpet! – Rebecca May 30 '10 at 13:08
  • A primary key needs to fulfill two requirements. First, it must be unique. Second, for normalization, all of the non-key elements must be fully dependent on the primary key. Some of your compound keys break normalization pretty badly, because it looks like one component of your compound key is dependent on the other part of the compound key. It's a major risk with compound keys. So to answer your concern, no, it is *not* just shoveling crap under the carpet! – Cylon Cat May 30 '10 at 20:19

5 Answers5

4

On a quick scan of EF's error messages, it clearly doesn't like the way you're setting up compound keys, and I think it's probably nudging you in the right direction. Give some thought again to what makes your primary keys unique. Is the OrderID alone not unique, without a CompanyID? Is a ProductID not unique, without a CompanyID? An OrderLine certainly should be unique without a CompanyID, since an OrderLine should be associated only with a single Order.

If you truly need the CompanyID for all of these, which probably means that the company in question is supplying you with ProductID and OrderID, then you might want to go a different direction, and generate your own primary keys that are not intrinsic to the data. Simply set up an auto-increment column for your primary key, and let these be the internal OrderID, OrderLineID, ProductID, CompanyID, etc. At that point, the OrderLine won't need the customer's OrderID or CompanyID; the foreign key reference to the Order would be its starting point. (And the CustomerID should never be an attribute of an order line; it's an attribute of the order, not the order line.)

Compound keys are just messy. Try designing the model without them, and see if it simplifies things.

Cylon Cat
  • 7,111
  • 2
  • 25
  • 33
  • I'm agreed on the compound keys. I'm not even sure why I added them in the first place! Late night programming is never a good idea for me. – Rebecca May 31 '10 at 11:41
  • I'm going to give the answer to Cylon Cat rather than EJB, mainly because he triggered my thought processes as to why I had added the compound keys in the first place (incorrectly). Thank you both. – Rebecca May 31 '10 at 11:44
  • 1
    I'm not agree. The cause of the error is the fields that Junto have not used when he create the relations betwen tables. Company Id in each table help a lot in multi-tenant sites. – Marco Staffoli Nov 12 '10 at 09:21
3

I think that the error is not in the design. Is not in the EF. Is in Sql Server relations.

Read the EF message:

The relationship 'FK_Order_Customer' uses the set of foreign keys '{CustomerId, CompanyId}' that are partially contained in the set of primary keys '{OrderId, CompanyId}' of the table 'Order'. The set of foreign keys must be fully contained in the set of primary keys, or fully not contained in the set of primary keys to be mapped to a model.

ERROR

Actualy the relation betwen Order and Customer use only one field (probably you dragged with the mouse the field "CustomerId" from teh Order table to the "Id" of the Customer table)

SOLUTION

Right click on the wire that connect Order and Customer and in the relation add also the CompanyId

PS: The design is correct.

Putting the CompanyId in each table is rith solution in multi-tenant architecture because help to scale (usualy always want to select only records from the loggedIn company).

Marco Staffoli
  • 2,475
  • 2
  • 27
  • 29
  • there is no absolute truth neither in statement "Not use compound keys" not in "Always use it for multitenancy". depends on database purpose and usage scenarious. in DWH databases I would like to use compound keys. in OLTP i probably would still create CompanyID column in each table which represents a root entity with an non clustered index but dont see why i need to make it a part of the key... and may be not to add to non-root entities like Order lines – Bogdan_Ch Mar 30 '12 at 03:45
2

I think storing the company number in each of the tables is hurting you more than helping. I can understand why you want to do this (as the programmer/dba you can just go into any table and 'see' what data belongs to who which is comforting), but it is getting in the way of you setting up the database the way it should be.

Avoid the compound keys and your design gets a whole lot simpler.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
0

If you have to absolutely add CompanyID column to each table, add it as a regular column and not a composite Key. Composite key is mostly used when you have to implement many to many relationship.

As someone mentioned also create a Non-clustered Index on CompanyID so joins to the Company table are benefitted.

Thanks!

Amol
  • 1
0

First: like others said, when referencing a foreign key, use the whole primary key in the other table (ie. both fields).

Second, I cannot imagine not using a CompanyID column in most tables in a serious application. Orderdetail might perhaps be an exception in this case (also global lookup tables perhaps, unless they are tenant dependant). Thing is, you cannot do any safe sort of free form search on a table without either adding the CompanyID, or doing JOINs up until the point you reach a table which has that column. The latter one obviously costs performance. Perhaps in this case you could make an exception for orderdetail and only search in the joined version (only two tables). Then again, its not really consistent.

Also regarding making a it a compound key or not: its possible, but opens up the possibility that a bug writes information incorrectly (into non existent, or other people's administrations) for the duration of the bug. Try to fix that in production, not to mention explain it to customers why there are seeing their competitors orders in their system.

IoTguy
  • 1
  • 1
  • After fiddling around with serveral ORM's, I have come to the conclusion that when using join tables in n:m relations, probably these could be excluded from having the tenantid (at least mostly). ORM's tend to give issues with this and really, any query involving a join table will have at least one other table containing a tenantid. Very rarely you would have to query these by themselves, easily solved by joining it to an adjacent table. – IoTguy Nov 27 '13 at 02:30