标准化跨表共享的通用 ID 类型

Question

这是问题的简化版本。

我们的客户向我们发送大量数据然后进行查询。 他们要求我们拥有几个“公共”ID，以便他们可以查询我们的数据。 （大多数人希望通过随数据一起发送的 ID 来查询我们的系统，但并非总是如此）。 为了简单起见，我们将它们称为“pid”、“crid”和“musicbrainzid”。 我们有一个“实体”表来存储这些信息。 它看起来像这样（“权威”是发送数据的人）：

entity 
-- 
entity_id   
authority  // who sent the data
type       // 'pid', 'crid', 'musicbrainz', etc.
value      // the actual id value

然后我们有单独的实体，例如“剧集”，“系列”和“广播”（实际上，还有更多，但我保持简单这里）。 其中每个都有一个指向实体表的entity_id。

外部客户如何通过 pid 或 crid 进行搜索并获得适当的剧集或连续剧，并正确识别它是什么？ 给定 pid，我们可以获取实体 id，但随后我们需要在剧集、系列和广播表中搜索该值。 此外，并非所有 id 都必然与所有其他表相关，但任何实体（例如，“剧集”）可能有多个 id（pid、crid 等）。

策略：

1. 查找 pid 的实体 id 并搜索pid 的所有其他表。
2. 在实体上放置一个“entity_type”列，但是如果它是剧集表中的 pid，但我们不小心将 Episode.type 设置为系列怎么办？ 我们不想重复数据，我不想将数据库元数据放入列值中。

选项 1 很慢并且似乎是错误的（此外，各个表具有不同的结构，这会产生问题）。

选项 2 意味着重复数据，并且该数据可能不同步。 我们可以使用触发器来强制执行此操作，但这看起来确实很令人讨厌，而且无论如何，mysql 触发器实现中的错误已经多次困扰我们。 我们现在正在使用这个策略，但没有触发器。

选项3是什么？

旁注：我们知道我们需要将“权限”分解到一个单独的表中，因为并非所有权限/类型组合都是有效的。

Answer 1

如果我正确理解了你的问题，我会选择选项 1。

基于entity_id 识别行的查询不应该那么慢，因为所有数据都应该在索引中。
如果您的索引配置正确，这甚至不应该访问实际数据。
（至少在 SQL Server 中不会。）

我要做的一个小更改是创建一小组表来标识哪些 id 对哪些表有效。
然后，您可以使用它来缩小需要搜索的表的范围。

选项 1 或 2 的替代方案可能是完全更改数据库结构，在同一个表上存储不同的数据，使用entity_id 作为主键，并使用包含数据的通用列。
这肯定会更激进，但我发现它对于像您这样的系统来说效果很好，其中数据及其结构非常动态。