database - 如何存储“主要”记录

假设我有下列表格

Companies
--CompanyID
--CompanyName

和

Locations
--LocationID
--CompanyID
--LocationName

每家公司至少有一个地点。我想跟踪每个公司的primary位置（是的，每个公司只有一个主要位置）。最好的方法是什么？在primaryLocationID表中添加一个Companies？

最佳答案

在“公司”表中添加PrimaryLocationID？
是，但这会创建循环引用，从而阻止您插入新数据：
解决这个鸡和蛋问题的一种方法是让Company.PrimaryLocationID保持为空，这样就可以暂时禁用其中一个循环fks。不幸的是，这意味着数据库将只执行“1:0..1”，而不是严格的“1:1”关系（因此您必须在应用程序代码中执行它）。
但是，如果您的dbms支持延迟约束（例如oracle或postgresql），那么您只需延迟其中一个fks就可以在事务仍在进行时打破循环。交易结束时，两个FK都必须到位，从而形成真正的“1:1”关系。
另一种解决方案是在Locations表中为主位置设置一个标志，并为空的非主位置（注意U1，表示唯一约束，确保公司不能有多个主位置）：

CREATE TABLE Location (
    LocationID INT PRIMARY KEY,
    CompanyID INT NOT NULL, -- References Company table, not shown here.
    LocationName VARCHAR(50) NOT NULL, -- Possibly UNIQUE?
    IsPrimary INT CHECK (IsPrimary IS NULL OR IsPrimary = 1), -- Use a BIT or BOOLEAN if supported by your DBMS.
    CONSTRAINT Locations_U1 UNIQUE (CompanyID, IsPrimary)
);

不幸的是，这有一些问题：
即使在支持延迟约束的dbms上，它也只能保证达到“1:0..1”（但不能保证真正的“1:1”）。
它需要一个额外的索引（支持惟一约束）。每个索引都会带来一定的开销，主要是为了提高插入/更新/删除性能。此外，clustered tables中的二级指标包含pk的拷贝，这可能使它们比预期的“胖”。
它依赖于符合ANSI的复合唯一约束，如果任何字段（但不一定全部）为空，则允许重复行。不幸的是，并不是所有的dbms都遵循这个标准，所以在oracle或ms sql server下，上面的方法是不可行的（但在postgresql和mysql下是可行的）。您可以使用一个filtered唯一索引而不是唯一约束来解决这个问题，但并非所有的dbms都支持这一点。
模型m:n，而您的需求似乎是1:n。尽管如此，该模型可以通过在{LocationID}上放置一个键（在{CompanyID, TypeOfLocation}上放置一个键以确保同一公司不能有同一类型的多个位置）被“强制”为1:n，但可能是为一个简单的“is primary”需求而过度设计的。