1

Refering to this post Entity Framework and multi-tenancy database design, the problem of multitenancy with tenant id propagated to multiple tables bringd issues with multiple cascade path.

Indeed in the post, an OrderLine can reference a Product belonging to a Company which is different than the Company of the Customer having the Order and this is not what you want (unless the same product catalog is shared by ALL companies but that is very unlikely in practice).

Therefore you may want to have CompanyID as part of the PKs so that when defining the FKs it ensures that the same CompanyID is consistent all the way down to OrderLine.

The problem is that you need compound key and not all ORM can handle it. Also this architecture starts to get complexe if you have a table that needs to reference itself (for example an Order depending on another Order).

To keep a shema simple while being able to overcome the issue mentioned before, I could only see a design like 1 tenant per DB.

But if somebody had a better idea is gladly welcomed.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Guest975
  • 11
  • 3
  • `Indeed in the post, an OrderLine can reference a Product belonging to a Company which is different than the Company of the Customer having the Order` -- Not unless your program has a bug in it. – Robert Harvey Nov 15 '20 at 16:08
  • Given the number of solutions that can interface your database I think it is the responsibility of the DB to avoid discrepancy of CompanyID to occur. Also the need to reimplement this test logic in all of ibterfaces there are a high pssibility that bug occur . Maybe a trigger can be used instead of the id propagation.... – Guest975 Nov 15 '20 at 16:11
  • Not particularly elegant, is it? All the database can do is throw. And you'll have to fix the bug anyway. – Robert Harvey Nov 15 '20 at 16:13
  • In any case, have a look here: https://dba.stackexchange.com/questions/198406 – Robert Harvey Nov 15 '20 at 16:16

0 Answers0