数据库设计中的冲突愿望，具有两个相似功能的字段

Question

好的，我现在正在为“盒子物品”制作一张表格。

现在，一个盒子物品，根据它的用途/物品的状态，最终可能会与“运输”盒子或“退货”盒子相关。

盒子物品可能有缺陷：如果是，则会在盒子物品的行中设置一个标志（IsDefective），并且盒子物品将被放入“退货”框中（与其他物品一起退回给该供应商）。 否则，盒子物品最终将被放入“运输”盒子中（与其他要运输的物品一起）。 （请注意，运输和退货盒子有自己的表格：所有盒子都没有一个通用的表格......尽管也许我应该考虑这样做，如果可能的话作为第三种可能性？）

也许我只是今天没有想清楚，但我开始质疑在这种情况下应该做什么。

我的直觉告诉我，我应该为每个可能的关系拥有一个单独的字段，即使在任何给定时间只能发生其中一个关系，这将使 Box Items 的架构看起来像：

BoxItemID 描述 有缺陷 运输箱ID 退货箱ID 等等...

这将使关系变得清晰，但似乎很浪费（因为任何时候都只会使用其中一个关系）。 于是我想我可以只有一个 BoxID 字段，并根据 IsDefective 字段确定它所指的是哪个 BoxID（运输箱 ID 或退货箱 ID）：

BoxItemID 描述 有缺陷 盒子ID 等等...

这似乎不那么浪费，但不适合我。 关系并不明显。

所以，我把它告诉你们，Stackoverflow 的数据库专家。 在这个情况下，你会怎么做？

编辑：谢谢大家的意见！ 这给了我很多思考。 首先，下次我开始这样的项目时我将使用 ORM。 =) 对于两个，因为我现在不在，所以我将咬住四个字节并使用两个字段。

再次感谢大家！

Answer 1

我和精神病毒液和马特兰在一起。

走多态路线（必须根据另一个字段的内容找出外键指向哪个表）将是一件痛苦的事情。 为此编写约束可能很困难（我不确定大多数数据库本身是否支持这一点，我认为您必须使用触发器）。

物品会在桌子之间移动吗？ 坚持使用两个具有相同定义的表，其中一个用于退货，一个用于运输可能是最简单的途径。 如果您想坚持最初提出的定义（具有两个单独的字段）是完全合理的。

“过早的优化是万恶之源”等等。 虽然这看起来很浪费，但请记住您存储的内容。 因为它们是 ID，所以它们可能只是整数，可能是 4 个字节。 每条记录浪费四个字节基本上不算什么。 事实上，由于填充将东西放在偶数地址或其他类似的东西上，因此可以“自由”地将额外的字段放在那里。 这一切都取决于数据库设计。

除非你有充分的理由走多态路线（比如你在一个内存很少的嵌入式系统上，或者你必须在一些非常慢的 9600bps 链路上进行复制），否则可能不值得你最终遇到的麻烦。 必须将所有这些特殊情况写入查询中可能会很烦人。

简单的例子：在两个表之间进行连接，如果你想要连接是基于是否设置了 isDefective 标志，这将是一件痛苦的事情。 能够单独使用两列之一可能足以省去很多麻烦，至少对我来说是这样。

Answer 2

我会考虑为盒子制作一个表格，盒子类型是盒子表的一列。 这将简化关系并使查询框类型变得容易。 因此，box 项只有一个 boxId 外键。

Answer 3

我会使用 Hibernate 所谓的 Table-per-subclass ，所以我的数据库最终会得到 3 个盒子表：Box、ShippingBox 和 ReturnBox。 BoxItem 中的 FK 将指向 Box。

Answer 4

你所说的是多态关系。 可以引用多个其他表的单个 ID。 有几个框架支持这一点，但是，它（可能）对数据库完整性不利（这可能是一个完全不同的讨论，您的数据库或应用程序是否应该保持引用完整性）。

那这个呢？

BoxItem:
BoxItemID, Description, IsDefective

Box:
BoxID, Description

BoxItemMap:
BoxID, BoxItemID, BoxItemType

然后，您可以将 BoxItemType 设为枚举或整数，您可以在应用程序中将常量定义为“退货”或“运输”作为盒子的类型。

Answer 5

同意上面的多态讨论，虽然它有可能使用得不好，但它仍然是一个可行的解决方案。

基本上你有一个称为 box 的基表。 然后你还有另外两个表，运输箱和退货箱。 这两个添加了对他们来说特殊的任何额外字段。 它们与具有 1:1 fk 的 box 相关。Boz 基表具有所有 box 类型的公共字段。

您将 BoxItem 与框表关联起来。 获得正确框类型的方法是执行一个查询，根据键将子框与根框连接起来。 基本框和子框中都有的记录就是这种类型。

您只需像前面提到的那样小心，当您创建一个框类型时，它就会正确完成。 但这就是测试的目的。 添加它们的代码只需要编写一次。 或者使用 ORM。

几乎所有的 ORM 都支持这个策略。

Answer 6

我只使用一个 BoxItems 表，其中包含 IsDefective、ShippingBoxID、运输箱相关字段、ReturnBoxID 和退货箱相关字段。 每条记录的某些字段始终为 NULL。

这是一个非常简单且不言而喻的设计，下一个开发人员不太可能被混淆。 理论上，这种设计效率低下，因为每行都保证有空字段。 在实践中，无论如何，数据库往往对每行都有所需的最小存储大小，因此（除非字段数量很大）这种设计无论如何都尽可能高效，并且更容易编码。

Answer 7

我可能会选择：

BoxTable:
box_id, box_descrip, box_status_id ...
     1, Lovely Box, 1
     2, Borked box, 2
     3, Ugly Box, 3
     4, Flammable Box, 4

       BoxStatus:
       box_status_id, box_status_name, box_type_id, ....
                   1,Shippable, 1
                   2,Return, 2
                   3,Ugly, 2
                   4,Dangerous,3

                BoxType:
                box_type_id, box_type_name, ...
                          1, Shipping box, ...
                          2, Return box, ....
                          3, Hazmat box, ...

这样，盒子状态定义了盒子类型，如果您稍后需要扩展到更多状态级别或盒子类型，它会很灵活。