Entity Framework和多租户数据库设计

Question

我正在研究一个面向SaaS概念的多租户数据库模式设计。它将采用ASP.NET MVC -> EF，但这不是那么重要。

下面您可以看到一个示例数据库模式（租户是公司）。CompanyId在整个模式中被复制，并且主键已经放置在自然键和租户ID上。

将此模式插入实体框架时，在将表添加到实体模型文件（Model1.edmx）中时会出现以下错误：

关系“FK_Order_Customer”使用了部分包含在表“Order”的主键集合“{OrderId，CompanyId}”中的外键集合“{CustomerId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_OrderLine_Customer”使用了部分包含在表“OrderLine”的主键集合“{OrderLineId，CompanyId}”中的外键集合“{CustomerId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_OrderLine_Order”使用了部分包含在表“OrderLine”的主键集合“{OrderLineId，CompanyId}”中的外键集合“{OrderId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_Order_Customer”使用了部分包含在表“Order”的主键集合“{OrderId，CompanyId}”中的外键集合“{CustomerId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_OrderLine_Customer”使用了部分包含在表“OrderLine”的主键集合“{OrderLineId，CompanyId}”中的外键集合“{CustomerId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_OrderLine_Order”使用了部分包含在表“OrderLine”的主键集合“{OrderLineId，CompanyId}”中的外键集合“{OrderId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。 关系“FK_OrderLine_Product”使用了部分包含在表“OrderLine”的主键集合“{OrderLineId，CompanyId}”中的外键集合“{ProductId，CompanyId}”。 外键集必须完全包含在主键集中，或者完全不包含在主键集中才能映射到模型。

这个问题分为两个部分：

1. 我的数据库设计是否有误？我应该避免使用复合主键吗？我对基本模式设计感到怀疑（头脑混乱综合症）。请随意建议“理想化”的模式。
2. 或者，如果数据库设计正确，那么 EF 是否无法匹配键，因为它将这些外键视为潜在的错误配置的 1:1 关系（不正确）？如果是这样，这是一个 EF 的 bug，并且我应该如何解决？

- Rebecca

---

如果我删除复合主键，只使用自然键（ProductId、OrderId、CustomerId、OrderLineId），EF错误就会消失。但是，我不确定这是否只是把问题搁置了一旁。 - Rebecca

一个主键需要满足两个要求。首先，它必须是唯一的。其次，为了规范化，所有非键元素都必须完全依赖于主键。你的一些复合键会破坏规范化，因为看起来复合键的一个组件依赖于另一个复合键的部分。这是使用复合键时的重大风险。所以，回答你的疑虑，不，这不仅仅是把问题藏起来! - Cylon Cat

Answer 1

在快速扫描EF的错误消息时，很明显它不喜欢你设置复合键的方式，我认为它可能在向你推荐正确的方向。再次考虑一下什么使你的主键唯一。如果只有OrderID没有CompanyID是不是唯一的？如果只有ProductID没有CompanyID是不是唯一的？一个OrderLine应该是唯一的，没有CompanyID，因为一个OrderLine应该只与单个Order相关联。

如果你确实需要所有这些的CompanyID，这可能意味着该公司正在向你提供ProductID和OrderID，那么你可能需要走另一条路，生成自己的主键，而不是内在于数据的主键。只需为您的主键设置一个自动增量列，让这些成为内部OrderID、OrderLineID、ProductID、CompanyID等。此时，OrderLine将不需要客户的OrderID或CompanyID；外键引用到Order将是其起点。(而CustomerID永远不应该是订单行的属性；它是订单的属性，而不是订单行的属性。)

复合键只会让事情变得混乱。尝试设计模型而不使用它们，看看是否简化了问题。

- Cylon Cat

---

我同意使用复合键。我甚至不确定为什么一开始要添加它们！深夜编程对我来说从来不是一个好主意。 - Rebecca

我将回答Cylon Cat而不是EJB，主要是因为他触发了我的思维过程，让我想到了为什么我一开始错误地添加了复合键。谢谢你们两个。 - Rebecca

我不同意。 错误的原因是Junto在创建表之间的关系时没有使用的字段。 每个表中的公司ID在多租户站点中非常有帮助。 - Marco Staffoli

Answer 2

我认为问题不在设计中，也不在EF上，而是在Sql Server关系中。

阅读EF消息：

“关系'FK_Order_Customer'使用了部分包含在表'Order'的主键集'{OrderId，CompanyId}'中的外键集'{CustomerId，CompanyId}'。 外键集必须完全包含在主键集中，或者完全不包含在主键集中，才能映射到模型。”

错误：

实际上，Order和Customer之间的关系仅使用一个字段（可能您用鼠标将Order表中的“CustomerId”字段拖动到Customer表的“Id”字段）。

解决方法：

右键单击连接Order和Customer的线，并在关系中添加CompanyId。

PS：设计是正确的。

在多租户架构中，在每个表中放置CompanyId是正确的解决方案，因为有助于扩展（通常只想选择登录公司的记录）。

- Marco Staffoli

---

在编写程序时，"不使用复合键"和"始终将其用于多租户"这两种说法都没有绝对的真理。这取决于数据库的目的和使用场景。在数据仓库数据库中，我想使用复合键。在 OLTP 中，我可能仍然会在每个表中创建一个代表根实体的 CompanyID 列，并为其创建一个非聚集索引，但我不明白为什么需要将其作为键的一部分...也许不需要添加到非根实体，如订单行。 - Bogdan_Ch

Answer 3

我认为在每个表中存储公司编号不仅没有帮助，反而会造成更多麻烦。我能理解你想这么做的原因（作为程序员/数据库管理员，你可以进入任何表格并“看到”属于谁的数据，这是让人感到安慰的），但这会妨碍你按照应有的方式设置数据库。

避免使用复合键，你的设计就会变得更加简单。

- E.J. Brennan

Answer 4

首先，像其他人所说的那样，在引用外键时，在另一个表中使用整个主键（即两个字段）。

其次，在大多数严肃应用程序的表中，我无法想象不使用CompanyID列。在这种情况下，Orderdetail可能是一个例外（也许还有全局查找表，除非它们是租户相关的）。问题是，如果没有添加CompanyID或者一直进行JOIN直到达到具有该列的表，您将无法对表执行任何安全的自由形式搜索。后者显然会影响性能。也许在这种情况下，您可以为orderdetail做出例外，并仅在连接版本（仅两个表）中进行搜索。但再次，这并不真正一致。

此外，关于将其设置为复合键与否：这是可能的，但会打开一个漏洞，使错误信息（写入不存在或其他人的管理）在漏洞持续时间内被写入。尝试在生产环境中修复它，更不用说向客户解释为什么他们在他们的系统中看到了竞争对手的订单。

- IoTguy

---

在尝试了几个ORM之后，我得出结论，在使用n:m关系中的连接表时，这些表可能被排除在租户ID之外（至少大多数情况下）。ORM在处理此类问题时往往会出现问题，而且任何涉及连接表的查询都将包含至少一个其他包含租户ID的表。很少情况下您需要单独查询它们，只需将其与相邻的表连接即可轻松解决。 - IoTguy

Answer 5

如果您必须向每个表添加CompanyID列，请将其作为常规列而不是复合键添加。当您必须实现多对多关系时，通常使用复合键。

正如某人提到的那样，还要在CompanyID上创建一个非聚集索引，以便受益于与Company表的连接。

谢谢！

- Amol