深入了解查找表

引言

在数据库上做出的基本设计选择会对您的应用程序产生深远影响。以简单的查找表为例。基本上有两种方法可以实现它们。第一种方法是为每种查找类型创建一个新表。第二种，通常称为“唯一查找表 (OTLT)”，是一个存储所有查找值的单个查找表。我一直为每种类型创建单独的查找表，但最近在一个项目中工作，该项目将所有查找表放入一个表中。我不得不说 OTLT 方法给我留下了不好的印象。我将解释为什么。

背景

一个大型应用程序可能包含数十个查找表。例如，一个 Address 表可能有一个用于 AddressType 的查找表。Address 表可能有一个 AddressTypeID 列，该列可以使用高效的 tinyint 数据类型，并且对 AddressType 表具有约束以确保数据完整性。现在，对于作为正确规范化数据库一部分所需的所有其他查找表，重复此操作多次。

听起来工作量很大，为什么我们不直接创建一个可以动态添加的超大查找表，即 OTLT 呢？当系统更改时，我们只需向 OTLT 添加一些新记录即可完成，无需创建任何新表。从表面上看，理论上这听起来不错。然而，当你深入挖掘并审视它在实践中如何运作时，OTLT 模型很快就会崩溃。

更大的问题

AddressType 表最多只有十几个记录，所以 tinyint 数据类型是一个不错的选择。如果我们使用 OTLT，主键将有远多于 256 个值，因此 Address 表和 OTLT 都必须使用更大的数据类型 (int 或 bigint)。这不是世界末日，但也不是效率的最佳选择。

所以我们向 OTLT 添加了五条地址类型记录。这些是首先添加的记录，所以生成的键是 0, 1, 2, 3, 4。接下来，你为另一个名为 ItemStatus 的表添加了一些更多的查找记录。这些记录的键是 5, 6, ...。同样，这不是世界末日，但很不方便。大多数查找表的键从 0 到 N 开始，它们很容易理解。现在你又添加了一条 AddressType 记录，它的键是 321，因为自从你第一次添加 AddressType 记录以来，表中已经添加了大量的记录。现在我们有一组 AddressType 键看起来像 0, 1, 2, 3, 4, 321。这简直是丑陋且非常烦人，当涉及到使用这些“魔术”数字时。调试和开发变得更加困难。将这种烦恼乘以你的 OTLT 中的查找表类型数量，它就不再是烦恼，而是一个问题了。

所以让我们通过向 OTLT 添加一个复合键来解决这个问题，该复合键包含一个用于外键的列，另一个用于 LookupType。现在你可以多次重用主键，因为查找值必须在两列中是唯一的。但是等等，现在可能有很多记录的查找值为 3。你如何确保当你将 OTLT 表连接到 Address 表时，你只获取 AddressType 记录？为什么不创建一个名为 AddressType 的视图，该视图仅过滤 OTLT 表中的 AddressType 记录，然后连接到该视图。此时，我问为什么不一开始就创建 AddressType 表呢？此外，如果正确规范化，将 LookupType 添加到 OTLT 可能需要另一个表。所以现在我们有一个查找表的查找表，这与 OTLT 模式的整个目的背道而驰。我称之为“解决方案分歧”，这清楚地表明你正在做错误的事情。

让我们继续解决 OTLT 问题。我们可能需要一个名为 CountryCode 的表，其中包含世界上所有国家/地区的记录，基于 ISO 3166 两字符代码。这将被连接到 Address 表中的 CountryCode 列。

OTLT 是以数字列作为键的。ISO 3166 代码作为国家/地区键是一个绝佳的选择，但它不起作用，因为它使用字符键。这个问题没有简单的答案。也许你可以在 OTLT 表中添加另一个基于两字符的键。现在我们真的失控了，因为你何时使用哪个键列？添加一个很少使用的特殊键列？我甚至不会提到 OTLT 键可能是一个将连接到数字列的 string 值，或者所有表中的所有键都应该是字符。这简直是疯言疯语。或者也许我们只是添加一个名为 CountryCode 的单独表。所以现在我们有一些查找在一个地方，其他查找在另一个地方。问题只会越来越糟。

由于 OTLT 表是通用的，因此列名也是通用的。例如，你可能有一个名为 ID、Value、Name 和 Description 的列。你需要在应用程序中显示地址类型的下拉列表。你会绑定到哪个列？ID 还是 Value？你会向用户显示哪个列？Value 还是 Description？根据数据添加到 OTLT 表的方式，答案可能会改变，而且对于不同的查找类型也可能不同。最大的问题是 OTLT 不会自文档化。我们并不真正知道这些值的含义，因为它们是脱离上下文存储的，即所有查找表的所有值都存储在一个表中，没有明确地与它们连接的表相关联。

这还带来了另一个令人烦恼的问题。在执行大型查询时，你最终会多次连接到同一个 OTLT 表以获取所需的查找值。这可能是一个真正的麻烦，因为如果不仔细构建查询，很容易获取错误的值。此外，作为开发人员，当我需要查看 AddressType 值时，我只需浏览 AddressType 表。如果它们在 OTLT 表中，我需要打开 OTLT 表，过滤记录以仅显示 AddressType 值，然后解释这些字段的含义。一天中多次重复此操作，这只会让你放慢速度。最重要的是，OTLT 难以使用。

使用 OTLT 还会出现性能问题，随着系统增长，这些问题会被发现。希望此时您已决定不走 OTLT 路线，并且将来不必处理性能问题。

摘要

下面是每种查找表模式的一些优点和缺点的快速总结。我确信还有更多，但这应该涵盖了最大的问题。

Issue	OTLT	独立表
数据完整性	困难	简单
自文档化	否	是
灵活	否	是
高效	否	是
可维护	简单	简单
易于使用	否	是
可扩展性/性能	差	Good

结论

尽管 OTLT 模型当时看起来是个好主意，但它为了表面上更简单的查找数据管理方式，牺牲了隐藏在表象之下的大量问题。如果你正在考虑使用 OTLT，请直接拒绝。如果出于某种原因你确实需要将所有值放在一个表中，只需创建一个单个视图来合并所有单独的查找表即可。