负整数索引：它们是邪恶的吗？

Question

我正在设计一个数据库，需要包含几十个表格，其中有我们提供的记录（一些默认值），也有用户可以添加的记录。为了防止用户自己给自己惹麻烦，必须禁止他修改默认记录。

有很多方法可以实现这一点，但我喜欢使用保护记录负整数索引的想法，同时将0作为无效的记录ID保留，并为用户记录分配正整数索引。

CREATE TABLE t1 (
    ixt1  integer AUTOINCREMENT,
    d1    double,
    CONSTRAINT pk_ixt1 PRIMARY KEY (ixt1),
    CONSTRAINT ch_zero CHECK (ixt1 <> 0)
);

-2 | 171.3 <- canned record
-1 | 100.0 <- canned record
 1 | 666.6 <- user record

以下是这种方法看起来不错的原因：

• 它不会使用更多的空间

• 很容易理解

• 不需要大量额外的表来实现

• "select * from table" 可以获取所有相关记录，没有额外的间接性

• 预设记录可以向负方向增长，用户记录可以向正方向增长

然而，我对数据库设计还比较陌生。在使用这种解决方案一段时间后，我开始担心使用负索引可能不好，因为

• 不同的DBMS可能不一致地支持负索引，使得编写与数据库无关的代码变得困难

• 通过在recid 0处插入某些内容，很容易搞砸事情

• 使用工具（例如db grids）可能很难使用带有非负值的整数索引。

也许还有其他一些非常明显的原因，这将使这成为一个非常糟糕的想法。

那么什么是最终答案？负整数索引是邪恶的吗？

- Matthew Lowe

---

我注意到你在主键上使用了 AUTOINCREMENT，这对你的策略有什么影响吗？你是手动计算所有新记录的正确主键，还是只为私有（负数）记录计算呢？ - Nicole

仅会显式设置私有（负）密钥。顺便提一下，我不知道这个特定的SQL语法在任何DBMS上是否可用。这只是为了说明。 - Matthew Lowe

Answer 1

这里最重要的缺陷是"智能密钥"的问题。在所有数据库中，负整数作为密钥是可行的。没有工具需要正整数索引值。这很容易出错，因为索引有一个不明显的“规则”，一旦你赢得了彩票并离开后，没有人会记得。此外，当你发明第三种状态码（预先制定的与客户特定的，产品线发明的另一种罐头与版本3之前的旧罐头）时，你注定会失败。使用"智能密钥"的问题在于，你要求密钥执行两个不相关的工作。

它是记录的唯一标识符，这就是密钥应该扮演的角色。同时，你还要求它提供状态、控制和授权以更改属性。这很危险。你不能扩展其含义，因为它是隐藏在密钥中的单个位。只需添加一个带有"所属者"的列。如果它由"神奇超级用户"拥有，则不向用户显示。如果你不能信任你的应用程序开发人员执行它，请使用视图来确保这一点。如果它归"神奇超级用户"所有，则它是默认数据，遵守该所有权的任何规则。

- S.Lott

---

+1 是为了让按键具有双重功能。按键就是按键，它不需要其他的功能。通过添加其他列来使行变得特殊。 - Parrots

不好的想法。负整数索引并不邪恶，但您的设计并不明显。“易于理解”，可能是。但从模式中立即显而易见，不是。最好有一个名为is_canned或is_protected的布尔/位列。 - Brian Pan

在数据库中声明为“布尔值”的任何内容都将变成多个值。 布尔值不应被视为持久的描述性属性；它是基于属性的条件，但不是一流属性。 - S.Lott

因此，这里的基本论点是使关键字执行两个任务是一个坏主意，因为它既不可扩展，也出乎意料。我同意这一点；这就是为什么我正在尽力使用整数键而不是UUID的原因。但是我有点难过，只是放弃并添加该额外列。使用正负键如此简洁，并将表细分得很好。它似乎应该是某种优雅的解决方案。=) - Matthew Lowe

"简洁" == 代码异味。在实现被嵌入另一个对象的值中时，要非常小心简洁。当您是Don Knuth编写可证明为最小的算法时，这是可以接受的。但对于您的问题（其约束不足），您无法证明这种“固定”/“用户”二元性是问题的持久特征。 - S.Lott

Answer 2

我曾经参与过一个非常庞大的计费系统。我们遇到了一个非常相似的问题……需要将一些记录标记为“特殊”。受影响的表格中，客户在他们的数据库中拥有数千万行现有数据，迁移所有数据到新结构（即添加一个列）被认为是不可接受的。

决定采用正如你所建议的方法。

问题在于，你需要让每一个业务逻辑都知道（并记住）负索引的特殊含义，并正确地处理它。从我的经验来看，这样做会带来很多错误风险。

除非你有非常特殊的情况，非常有利于采用这种非传统的方法，否则我建议你坚持使用更传统的额外列。这是大多数开发人员习惯于的方式，因此不太可能出现错误。我希望我们当时能够下决心添加额外的列。

- Eric J.

---

+1 表示“所有业务逻辑都必须知道特殊含义”。 - Bob Jarvis - Слава Україні

我非常欣赏这是来自经验的建议。但在我看来，添加一个额外的列在难度上应该是等价的。如果您使用了额外的列而不是使用负索引，您的代码会有什么不同呢？ - Matthew Lowe

我认为最大的问题是开发人员（现在和未来5年内维护此代码的人）期望特殊含义被建模为列，而不是将特殊解释应用于索引的数值。其他人也指出，如果您需要将来添加其他“特殊”含义，则无法扩展此设计，因此人们必须检查索引的符号并检查列是否存在任何新状态。 - Eric J.

因此，使用这种方法会使软件难以维护。我也同意这个观点，特别是因为你已经不得不处理替代方案。 - Matthew Lowe

Answer 3

这是您的数据，但我认为这不是一个好主意。像这样的“索引”值应该是没有意义的 - 不要使用符号或数字范围或其他任何东西来表示“某事”或“其他事情”。我认为从长远来看，您最好有一个“记录类型”列，清楚地指示您正在查看什么类型的记录。在我的经验中，这是一个更好的方法。

祝好运。

- Bob Jarvis - Слава Україні