database - 完全执行您的数据模型或不完全执行您的数据模型

Question

http://weblogs.sqlteam.com/jeffs/archive/2008/08/13.aspx :

Consider the following logical data model:
* There are multiple Companies
* Each Company has many Projects
* Each Project has many Tasks
* Each Task has a Status, selected from a global list of pre-defined Statuses.

Let us say that we decide that the primary key of Companies, Projects, Tasks, and Status are all Identity (auto-number) columns, since we would like to auto-generate primary keys for those tables.

所以基本上，我们有 4 个表:
状态(主键:StatusID)
公司(PK:CompanyID)
项目(PK:ProjectID，FK:[Companies].CompanyID)
任务(PK:TaskID，FK:[Projects].ProjectID，[Status].StatusID)。

Now, allow me to add one little wrinkle. Suppose that the available Statuses for each Task is not defined globally, but at the Company level. That is, each Company has its own list of Statuses to which a Task can be assigned.

This means that the Status table now needs a Foreign Key reference to the Companies table (to indicate which Company each Status belongs to):

公司(PK:CompanyID)
状态(PK:StatusID，FK:[Companies].CompanyID)
项目(PK:ProjectID，FK:[Companies].CompanyID)
任务(PK:TaskID，FK:[Projects].ProjectID，[Status].StatusID)。

Are there any other changes we need to make to this data model? Or is simply adding a CompanyID column to the Status table enough to facilitate this change? Remember, our goal as always is full referential integrity using primary and foreign key constraints wherever possible.

Well, there is an issue:

Nothing in this data model stops us from assigning a Status to a Task that is not defined for that Task's parent company. We have no way of enforcing this right now with our current constraints. Our physical data model is flawed.

This is very easily fixed, but it can only be done by violating the "all tables just need an identity primary key" rule.

First, remember this: just because an identity column is unique does not mean that that column cannot be part of a primary key.

他继续说明如何使用复合键来完全实现和约束您的数据模型，如下所示:

公司(PK:CompanyID)
状态(PK:CompanyID，StatusID，FK:[Companies].CompanyID)
项目(PK:CompanyID，ProjectID，FK:[Companies].CompanyID)
任务(PK:TaskID，FK:[Projects]。(CompanyID，ProjectID)，[Status]。(CompanyID，StatusID))。

长期以来，我一直热衷于完全执行/约束我的数据模型，但是，我经常发现自己处于与上述情况类似的情况，并且我走到了十字路口:

完全执行或不完全执行。

明显的缺点是看似过于复杂的设计。

现在，我知道不一定有“正确”的设计，但对于像这样的情况..我正在寻找最佳实践方面的反馈。

关于此设计或完全执行您的数据模型设计的优点、缺点和一般想法？

**请注意，这个问题可能会引发关于在执行数据模型(数据库或应用程序或两者)方面的责任在哪里的争论。为了便于讨论，我认为您的数据模型应该强制执行 - 请在此假设下回答。 **

Answer 1

我会创建一个 CompanyStatus 表，它是 Company 和 Status 之间的多对多表，并描述哪些状态适用于给公司。然后，任务会被分配一个 CompanyStatusID 而不是 StatusID。

这也可以防止您在 Status 表中出现重复状态 - 例如，许多公司可以共享相同的Closed 状态，这是更好的规范化。

因此，您无需使用复合键即可正确执行约束。我更喜欢使用没有意义的单个自动增量主键(代理键)。这比假设 key 是唯一的(例如 SSN)更稳健，因为事实总是有可能并非如此，但您必须存储数据，因为应用程序需要它(所以唯一约束在这里没有帮助)。