如何存储“主要”记录

Question

假设我有以下表格：

Companies
--CompanyID
--CompanyName

并且。

Locations
--LocationID
--CompanyID
--LocationName

每个公司都至少有一个地点。我想要跟踪每个公司的“主要”位置（是的，每个公司都将恰好有一个主要位置）。设置这个的最佳方法是什么？在“公司”表中添加一个“primaryLocationID”吗？

- Ben

Answer 1

在公司表中添加primaryLocationID吗？

是的，但这会创建一个循环引用，可能会阻止您插入新数据。

解决这个“鸡生蛋”的问题的一种方法是将 Company.PrimaryLocationID 设为可空，这样就可以暂时禁用其中一个循环外键。不幸的是，这意味着数据库只能强制实施“1:0..1”，而不能严格实施“1:1”关系（所以你必须在应用程序代码中强制执行它）。

然而，如果你的DBMS支持延迟约束（如Oracle或PostgreSQL），你可以简单地推迟其中一个FK来打破循环，同时事务仍在进行中。到事务结束时，两个FK都必须存在，从而产生真正的“1:1”关系。

---

另一种解决方案是在Locations表中设置一个标志来表示主要位置，并将非主要位置设置为NULL（请注意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)
);

不幸的是，这存在一些问题：

• 即使在支持延迟约束的数据库管理系统上，它也只能保证最多"1:0..1"（但不是真正的"1:1"）。
• 它需要一个额外的索引（以支持唯一约束）。每个索引都会带来一定的开销，主要是对插入/更新/删除性能的影响。此外，在聚簇表中的二级索引包含PK的副本，这可能会使它们比预期更"胖"。
• 它依赖于符合ANSI标准的复合唯一约束，如果任何（但不一定是所有）字段为空，则允许重复行。不幸的是，并非所有的DBMS都遵循该标准，因此上述方法在Oracle或MS SQL Server下不能直接使用（但在PostgreSQL和MySQL下可以使用）。您可以使用过滤唯一索引代替唯一约束来解决这个问题，但并非所有DBMS都支持。

---

该段文字的大意是：BaBL86's solution模型是M:N，而你的要求似乎是1:N。尽管如此，该模型可以通过在{LocationID}上放置一个键（并在{CompanyID，TypeOfLocation}上放置一个键，以确保同一公司没有相同类型的多个位置），但这可能对于一个简单的“主要”要求来说过于复杂了。

- Branko Dimitrijevic

Answer 2

我认为你自己的解决方案是最好的——这样可以确保每个公司只有一个主要位置。通过将其定义为NOT NULL列，您甚至可以强制每个公司都必须有一个主要位置。

使用 BaBL86的解决方案，您没有这些限制：一个公司可以拥有0到无限个“主要位置”，这显然是不应该发生的。

请注意，如果您使用外键约束并将primaryLocationID定义为NOT NULL列，您将遇到问题，因为您基本上有一个循环（位置指向公司，公司指向位置）。您无法创建新公司（因为它需要一个主要位置），也无法创建新位置（因为它需要一个公司）。

- Nic Wortel

Answer 3

我用数据透视表来完成：

CompanyLocations
    --CompanyID
    --LocationID
    --TypeOfLocation (primary, office, warehouse etc.)

在这种情况下，您可以选择所有位置，然后根据需要使用类型。如果您创建PrimaryLocationID，则需要对一个表进行两个连接和更复杂的逻辑。这比此更糟糕。

- BaBL86

---

一个位置既可以是主要的，也可以是仓库——所以这种方法并不完全可行。我考虑过类似的解决方案——对于位置表中的每条记录都有一个布尔字段“primaryLocation”，但这似乎并不比在公司表中有一个primaryLocationID更好。 - Ben

如果您有一些复选框，您可以使用二进制掩码（如Unix ACL）：第一位是主要标志，第二位是仓库，第三位是办公室，而在这种情况下，TypeOfLocation为：1-主要，2-仓库，3-主要和仓库，4-办公室，5-办公室和主要，6-办公室和仓库，7-办公室、仓库和主要。了解BEETWISE操作：例如(TypeOfLocation&4=4)。 - BaBL86