如何创建多个一对一

Question

我有一个包含许多表的数据库，除了一点之外，它看起来都很好...

Inventory Table <*-----1> Storage Table <1-----1> Van Table
                              ^
                              1
                              |-------1> Warehouse Table

使用存储表，因为货车和仓库表相似，但如何在存储和仓库/货车表之间创建关系？它们需要 1 对 1 才有意义，因为存储对象只能有 1 个存储位置和类型。 我确实将 Van/Warehouse 表链接到 StorageId 主键，然后添加一个约束以确保 Van 和 Warehouse 表没有相同的 StorageId，但这似乎可以用更好的方法来完成。

我可以看到几种方法，但它们似乎都是错误的，所以任何帮助都会很好！

Answer 1

您正在使用继承（在实体关系建模中也称为“子类”或“类别”）。一般来说，有3种方式在数据库中表示它：

1. “一张表中的所有类”： 只有一张表“覆盖”父类和所有子类（即包含所有父类和子列） ），使用 CHECK 约束来确保字段的正确子集非 NULL（即两个不同的子项不会“混合”）。
2. “每个表的具体类”：为每个子项提供一个不同的表，但没有父表。这需要在所有孩子中重复父母的关系（在您的情况下库存<-存储）。
3. “每个表的类”：为每个子表提供一个父表和一个单独的表，这就是您想要做的。这是最干净的，但可能会消耗一些性能（主要是在修改数据时，在查询时不是那么多，因为您可以直接从子级加入并跳过父级）。

我通常更喜欢第三种方法，但在应用程序级别强制执行子项的存在和排他性。在数据库级别强制执行这两种方法有点麻烦，但如果 DBMS 支持延迟约束，则可以完成。例如：

CHECK (
    (
        (VAN_ID IS NOT NULL AND VAN_ID = STORAGE_ID)
        AND WAREHOUSE_ID IS NULL
    )
    OR (
        VAN_ID IS NULL
        AND (WAREHOUSE_ID IS NOT NULL AND WAREHOUSE_ID = STORAGE_ID)
    )
)

这将强制执行排他性（由于 CHECK）以及孩子的存在（由于 CHECK 和 FK1/FK2 的组合）。

不幸的是，MS SQL Server不支持延迟约束，但您也许可以将整个操作“隐藏”在存储过程后面并禁止客户端直接修改表。

---

只需强制执行排他性，无需延迟约束：

STORAGE_TYPE 是一种类型鉴别器，通常是一个整数以节省空间（在上面的示例中，0 和 1 对于您的应用程序来说是“已知的”并进行相应的解释）。

VAN.STORAGE_TYPE 和 WAREHOUSE.STORAGE_TYPE 可以计算（又名“已计算”）列，以节省存储空间并避免需要 CHECK s。

--- 编辑 ---

计算列将在 SQL Server 下工作，如下所示：

CREATE TABLE STORAGE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE tinyint NOT NULL,
    UNIQUE (STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE VAN (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(0 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE WAREHOUSE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(1 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

-- We can make a new van.
INSERT INTO STORAGE VALUES (100, 0);
INSERT INTO VAN VALUES (100);

-- But we cannot make it a warehouse too.
INSERT INTO WAREHOUSE VALUES (100);
-- Msg 547, Level 16, State 0, Line 24
-- The INSERT statement conflicted with the FOREIGN KEY constraint "FK__WAREHOUSE__695C9DA1". The conflict occurred in database "master", table "dbo.STORAGE".

不幸的是，SQL Server 需要在外键中使用的计算列才能持久化。其他数据库可能没有这个限制（例如Oracle的虚拟列），这样可以节省一些存储空间。

Answer 2

正如你所说，解决方案有很多种。我建议从最简单的解决方案开始，然后在性能或存储出现问题时进行优化。最简单的解决方案（但在存储方面不是最佳解决方案）是拥有一个存储表，其中包含存储类型列（指示该行代表货车还是仓库），以及货车属性和仓库属性列。在代表货车的行中，仓库属性的列全部为空。在代表仓库的行中，货车属性的列全部为空。

这样，您就可以减少表的数量，并使查询保持良好和简单。如果存储变得紧张，请准备好重新考虑您的决定。

Answer 3

在我看来，库存物品可能会改变位置，所以我会选择这样的东西。