entity-framework - Entity Framework 和 Multi-Tenancy 数据库设计

Question

我正在研究 SaaS 概念的 Multi-Tenancy 数据库架构设计。它将是 ASP.NET MVC -> EF，但这并不重要。

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

当我将表添加到实体模型文件 (Model1.edmx) 时，将此架构插入 Entity Framework 会出现以下错误:

• 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.

问题分为两部分:

我的数据库设计不正确吗？我应该避免使用这些复合主键吗？我在质疑我对基本模式设计(疲惫的大脑综合症)的理智。请随时提出“理想化”模式。

或者，如果数据库设计正确，那么 EF 是否无法匹配键，因为它将这些外键视为潜在的错误配置的 1:1 关系(错误地)？在这种情况下，这是一个 EF 错误，我该如何解决？

Answer 1

在快速浏览 EF 的错误消息时，它显然不喜欢您设置复合键的方式，我认为它可能会插入您朝着正确的方向前进。再想想是什么让您的主键独一无二。如果没有 CompanyID，OrderID 本身就不是唯一的吗？如果没有 CompanyID，ProductID 是否不是唯一的？没有 CompanyID 的 OrderLine 当然应该是唯一的，因为 OrderLine 应该只与单个 Order 相关联。

如果您真的需要所有这些的 CompanyID，这可能意味着有问题的公司正在为您提供 ProductID 和 OrderID，那么您可能想要转向不同的方向，并生成您自己的非数据固有的主键.只需为您的主键设置一个自动递增列，并让它们成为内部 OrderID、OrderLineID、ProductID、CompanyID 等。此时，OrderLine 将不需要客户的 OrderID 或 CompanyID；对订单的外键引用将是其起点。 (并且 CustomerID 永远不应该是订单行的属性；它是订单的属性，而不是订单行。)

复合键只是乱七八糟。尝试在没有它们的情况下设计模型，看看它是否可以简化事情。