实体框架和多租户数据库设计
我正在研究 SaaS 概念的多租户数据库架构设计。这将是 ASP.NET MVC -> EF，但这并不那么重要。
下面您可以看到一个示例数据库架构（租户是公司）。 CompanyId 在整个架构中进行复制，并且主键已放置在自然键和租户 ID 上。
当我将表添加到实体模型文件 (Model1.edmx) 中时，将此架构插入实体框架会出现以下错误：
- 关系“FK_Order_Customer”使用外键集“{CustomerId, CompanyId}”，这些外键集部分包含在表“Order”的主键集“{OrderId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_OrderLine_Customer”使用外键集“{CustomerId, CompanyId}”，这些外键集部分包含在表“OrderLine”的主键集“{OrderLineId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_OrderLine_Order”使用外键集“{OrderId, CompanyId}”，这些外键集部分包含在表“OrderLine”的主键集“{OrderLineId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_Order_Customer”使用外键集“{CustomerId, CompanyId}”，这些外键集部分包含在表“Order”的主键集“{OrderId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_OrderLine_Customer”使用外键集“{CustomerId, CompanyId}”，这些外键集部分包含在表“OrderLine”的主键集“{OrderLineId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_OrderLine_Order”使用外键集“{OrderId, CompanyId}”，这些外键集部分包含在表“OrderLine”的主键集“{OrderLineId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
- 关系“FK_OrderLine_Product”使用外键集“{ProductId, CompanyId}”，这些外键集部分包含在表“OrderLine”的主键集“{OrderLineId, CompanyId}”中。外键集必须完全包含在主键集中，或者完全不包含在要映射到模型的主键集中。
问题分为两部分：
- 我的数据库设计不正确吗？我应该避免使用这些复合主键吗？我质疑我对基本模式设计的理智（疲劳大脑综合症）。请随意提出“理想化”模式。
- 或者，如果数据库设计正确，那么 EF 是否无法匹配键，因为它将这些外键视为潜在的错误配置的 1:1 关系（不正确）？在这种情况下，这是 EF 错误吗？我该如何解决它？
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:
- 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.
- 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
快速浏览一下 EF 的错误消息，它显然不喜欢您设置复合键的方式，我认为它可能会引导您走向正确的方向。再次思考一下是什么让你的主键独一无二。如果没有 CompanyID，OrderID 是否唯一？如果没有 CompanyID，ProductID 是否不唯一？在没有 CompanyID 的情况下，OrderLine 当然应该是唯一的，因为 OrderLine 应该仅与单个订单相关联。
如果您确实需要所有这些的 CompanyID，这可能意味着相关公司正在向您提供 ProductID 和 OrderID，那么您可能需要采取不同的方向，并生成您自己的非数据固有的主键。只需为主键设置一个自增列，并将其设置为内部 OrderID、OrderLineID、ProductID、CompanyID 等。此时，OrderLine 将不需要客户的 OrderID 或 CompanyID；对订单的外键引用将是其起点。 （并且 CustomerID 永远不应该是订单行的属性；它是订单的属性，而不是订单行。）
复合键太混乱了。尝试在没有它们的情况下设计模型，看看它是否可以简化事情。
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.
我认为错误不在于设计。
不在EF中。
处于 Sql Server 关系中。
读取 EF 消息：
错误
实际上订单和客户之间的关系只使用一个字段（可能您用鼠标将订单表中的“CustomerId”字段拖到了客户表的“Id”中）
解决方案< /strong>
右键单击连接 Order 和 Customer 的连线，并在关系中添加 CompanyId
PS：设计是正确的。
将 CompanyId 放入每个表中是多租户架构中的解决方案，因为帮助扩展（通常总是只想选择来自登录公司的记录）。
I think that the error is not in the design.
Is not in the EF.
Is in Sql Server relations.
Read the EF message:
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).
我认为将公司编号存储在每个表中对您的伤害大于帮助。我可以理解你为什么要这样做（作为程序员/数据库管理员，你可以进入任何表并“查看”哪些数据属于谁，这是令人欣慰的），但它妨碍了你设置数据库应该如此。
避免使用复合键，您的设计会变得更加简单。
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.
如果必须将 CompanyID 列绝对添加到每个表中，请将其添加为常规列而不是复合键。当您必须实现多对多关系时，通常会使用复合键。
正如有人提到的，还在 CompanyID 上创建非聚集索引，因此与 Company 表的连接会受益。
谢谢！
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!
第一：就像其他人所说，引用外键时，使用另一个表中的整个主键（即两个字段）。
其次，我无法想象在严肃的应用程序中的大多数表中不使用 CompanyID 列。在这种情况下，Orderdetail 可能是一个例外（也可能是全局查找表，除非它们依赖于租户）。问题是，如果不添加 CompanyID 或执行 JOIN 直到到达具有该列的表，您就无法对表进行任何安全的自由形式搜索。后者显然会降低性能。也许在这种情况下，您可以对 orderdetail 进行例外处理，并仅在连接版本（仅两个表）中进行搜索。话又说回来，它并不真正一致。
另外，关于是否将其设置为复合键：这是可能的，但会导致错误在错误期间错误地写入信息（写入不存在的或其他人的管理中）。尝试在生产中解决这个问题，更不用说向客户解释为什么在他们的系统中看到竞争对手的订单了。
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.