SQL:引用一对多

Question

我遇到了一些人所说的相当奇怪的问题。

假设我有一个表，它可能引用许多其他表中的一个(且仅一个)。我将如何以最佳方式做到这一点？我正在寻找一种适用于大多数数据库(MS SQL、MySQL、PostgreSQL 等)的解决方案。在我看来，有几个不同的解决方案(有比另一个更好的吗？):

1. 每个可能的引用都有一栏。这些列中只有一列可能包含任何给定行的值，所有其他列都为空。允许使用严格的外键，但是当“很多”(可能引用的表)的数量变大时会变得乏味
2. 具有两列关系，即一列“描述”引用了哪个表，一列引用实​​例(该表中的行)。当“许多”(引用表)的数量增加时很容易扩展，尽管我不能以直接的方式执行单个查询查找(要么左连接所有可能的表，要么联合多个查询，每个查询连接到一个表)
3. ??

有道理吗？在这种情况下，最佳做法(如果有的话)是什么？我特别希望能够从引用的实体查询数据，而无需真正知道引用了哪些表。

你会怎么做？

Answer 1

这两种方法在任何关系型数据库中都适用，所以你不必担心那个考虑。两者都会导致相当麻烦的查询。对于第一种方法:

select . . .
from t left outer join
     ref1
     on t.ref1id = ref1.ref1id left outer join
     ref2
     on t.ref2id = ref2.ref2id . . .

对于第二种方法:

select . . .
from t left outer join
     ref1
     on t.anyid = ref1.ref1id and anytype = 'ref1' left outer join
     ref2
     on t.anyid = ref2.ref2id and anytype = 'ref2' . . .

因此，从查询简单性的角度来看，我看不出两者有什么主要优势。第二个版本有一个小缺点——在编写查询时，您必须记住连接的名称。随着时间的推移，这可能会丢失。 (当然，您可以使用约束或触发器来确保只有一组固定的值进入列。)

从查询性能来看，第一个版本有很大的优势。您可以将该列标识为外键， 数据库可以对其进行统计。例如，这可以帮助数据库选择正确的连接算法。第二种方法不容易提供这种可能性。

从数据大小的角度来看，第一个版本需要存储每个可能值的id。第二个更紧凑。从可维护性的角度来看，首先是难以添加新的对象类型；第二个很简单。

如果你有一组彼此相似的东西，那么你可以考虑将它们存储在一个表中。不合适的属性可以被 NULLed 掉。您甚至可以为事物的不同风格创建 View 。一张 table 可能是也可能不是一种选择。

换句话说，这个问题没有正确答案。与数据库设计的许多方面一样，这取决于数据的使用方式。在没有其他信息的情况下，我可能会首先尝试将数据强制到一个表中。如果这不合理，我会选择第一个选项，如果可以用一只手数清 table 的数量，如果有更多的 table ，我会选择第二个。