主页 / dba / 问题 / 108210
Accepted
rubik
Asked: 2015-07-28 05:39:25 +0800 CST

将除一列以外的所有列标记为主键是否合理？

  • 772

我有一张代表电影的桌子。这些字段是：
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

我的数据库不能被重复的行污染，所以我想强制唯一性。问题在于不同的电影可能具有相同的标题，甚至除了tags和之外的相同字段downloads。如何实现唯一性？

我想到了两种方法：

  • 制作除downloads主键之外的所有字段。我一直在downloads外面，因为它是 JSON，它可能会影响性能。
  • 仅保留id作为主键，但为所有其他列添加唯一约束（再次除外downloads）。

我读了这个非常相似的问题，但我不太明白我应该怎么做。目前此表与任何其他表都不相关，但将来可能会。

目前我的记录略少于 20,000 条，但我希望这个数字会增长。我不知道这是否与这个问题有些相关。

编辑：我修改了架构，这是我创建表的方式：

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

我还添加了该timestamp列，但这不是问题，因为我不会碰它。所以它将永远是自动的和唯一的。

database-design postgresql

3 个回答

  1. 想象一下，您和一群朋友外出，谈话变成了电影。有人问，你觉得《三剑客》怎么样？你回答说：“哪个？”

    您需要哪些额外信息才能绝对确定你们都在考虑同一部电影？导演的名字？制作工作室？发行年份？明星的名字之一？两个或更多的某种组合？

    我的问题和你的问题的答案是一样的。

    但是，我认为该类型不是一个好的候选者。原因之一，流派是一个过于主观的标准。《三剑客》是动作片吗？戏剧？冒险？喜剧？动作冒险？浪漫喜剧？我经常看到同一部电影列在不同的类型下。即使您允许多种类型，您的用户也可能会选择完全不同的类型，而这些类型未与他们正在寻找的实际电影一起列出。

    甚至运行时也可能不同，尤其是在影院和 VCR/DVD/b 光版本之间。

    因此，您需要坚硬、客观的属性，这些属性不会因媒体发布而改变。不幸的是，这可能会排除电影的名称，因为已知电影会重新命名，尤其是在续集发行之后。

    发布日期呢？1993 年上映？1999 年的 VCR 版本？2004 年的 DVD 发行版？你明白了。

    想一想，艾伦·史密西 (Alan Smithee) 执导的那些电影有哪些？事后，真正的导演是否最终挺身而出，将自己的名字列入项目？我不知道。

    嗯，我最好在还有一些标准时停下来。

    一些额外的要点：

    • 是的，保留代理键并在自然键字段上创建唯一索引（如果您最终可以确定它们的话）。代理键最适合外键引用。您不想在每个包含对电影的引用的表中复制所有自然键字段。
    • 删除数组字段（流派、标签、来源）。继续并正确规范化这些属性。我从来没有见过一个数组字段不是比它的价值更麻烦，特别是如果您希望它们是可搜索的（“...where genre = 'horror'...”）。请注意，这不会自动消除任何大小写差异和拼写问题（“Science Fiction”与“SciFi”）——除非您正确维护查找表。但是，在小表的一个字段中检查此类差异比在大表的每一行的每个数组单元格中检查要容易得多。
    • 6

  2. 当涉及到您想要/需要强制执行的唯一性时，ID 列没有任何优势。永远不会通过添加无意义的 ID 来强制执行任何属性组合的唯一性。它的“优势”仅在您到达需要一个需要外键的新表的地步时才会显示出来。在这种情况下，如果您包含了 Id，那么您可以将其用作新表中的 FK。（但不要认为这会是免费的午餐。这种方法的缺点是您可能会发现自己编写更多连接仅仅是为了获取信息，而这些信息很可能是您创建的新表的一部分。 )

    • 4
  3. Best Answer

    您的表定义现在看起来很合理。对于所有列NOT NULL，UNIQUE约束都将按预期工作——除了拼写错误和细微的拼写差异，我担心这可能很常见。考虑@a_horse 的评论。

    替代功能唯一索引

    另一个选项是功能唯一索引（类似于@Dave 评论的内容）。但我会使用一种uuid数据类型来优化索引大小和性能。

    从数组到文本的转换不是IMMUTABLE（由于其通用实现）：

    因此你需要一些辅助函数来声明它不可变：

    CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                      , _runtime int2
                                      , _released_in int2
                                      , _genres text[]
                                      , _tags text[]
                                      , _origin text[])
  RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
         || _genres::text || _tags::text || _origin::text)::uuid';

    将它用于索引定义：

    CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

    SQL小提琴。

    更多细节：

    您可以将生成的 UUID 用作 PK，但我仍会使用serial其 4 个字节的列，这对于 FK 引用和其他目的来说既简单又便宜。对于需要独立生成 PK 值的分布式系统，UUID 是一个很好的选择。或者对于非常大的桌子，但在我们的太阳系中没有足够的电影。

    优点和缺点

    唯一约束是通过有关列的唯一索引实现的。首先将相关的列放在约束定义中，您就有了一个有用的索引用于其他目的作为附带利益。

    还有其他具体的好处，这里是一个列表：

    功能唯一索引的大小（可能小得多）小，这可以使其速度大大加快。如果您的列不是太大，则差异不会太大。计算的开销也很小。

    连接所有列可能会引入误报 ( 'foo ' || 'bar' = 'foob ' || 'ar'，但在这种情况下这似乎不太可能。拼写错误的可能性更大，您可以在这里安全地忽略它。

    唯一性和数组

    数组必须一致地排序才能在任何依赖于运算符的独特排列中有意义，=因为'{1,2}' <> '{2,1}'. 我建议使用PK和唯一条目查找表genre，这允许对数组元素进行模糊搜索。然后：tagoriginserial

    无论哪种方式，直接使用数组或使用规范化模式和物化视图，使用正确的索引和运算符搜索都可以非常有效：

    在旁边

    如果您使用的是 Postgres 9.4 或更高版本，请考虑jsonb使用json.

    • 4

相关问题