sql - 基于分层模式中外键的滚动 id

Question

例如，请考虑这个分层架构。

假设所有 id 字段都是自动递增的主键，并且外键按 [parent_table_name]_id 约定命名。

问题

只要数据库中有多个公司，公司就会共享它们之间的所有主键序列。

例如，如果有两个 company 行，则 customer_group 表可能如下所示

| id | company_id |
-------------------
| 1  |     1      |
| 2  |     1      |
| 3  |     2      |
| 4  |     2      |
| 5  |     1      |
-------------------

但它应该是这样的

| id | company_id |
-------------------
| 1  |     1      |
| 2  |     1      |
| 1  |     2      |
| 2  |     2      |
| 3  |     1      |
-------------------

对于customer 和树中直接或间接引用company 的任何其他表，也应该表现出这种行为。

请注意，我很可能会为此创建第二个 id 列(名称类似于 relative_id)，同时保留唯一的 id 列完好无损，因为这实际上主要是为了显示目的以及用户将如何引用这些数据实体。

---

现在，如果这只是一层层次结构，那将是一个相对简单的解决方案。我可以制作一个表(table_name、company_id、current_id)和一个在插入任何表之前触发的触发器过程，将当前 id 递增 1 并将行的 relative_id 设置为该值。当 company_id 就在插入查询中时，这很简单。

但是那些不直接引用 company 的表呢？就像本例中层次结构的最低级别 workorder，它仅引用 customer。
是否有一个干净、可重复使用的解决方案来从“customer_id”一路攀登到最终检索父 company_id？

在性能方面，在每个 INSERT 上使用 SELECT 递归地向上递归对我来说并不太吸引人。

我也不喜欢只为每个表添加公司外键的想法，每增加一个表，架构就会变得越来越丑陋。

但这是我能看到的两个解决方案，但我可能没有找对地方。

Answer 1

如果您使用生成的 key ，公司不应该关心主键是什么。它们应该是毫无意义的；比较平等，没有别的。我 grumbled about this earlier , 所以我很高兴看到你写:

Note that I will most likely make a second id column (named something like relative_id) for this purpose, keeping the unique id column intact, as this is really mostly for display purposes and how users will reference these data entities.

你做得对。

大多数时候，ID 是什么并不重要，因此您可以只给他们序列中出现的任何内容，而不用关心漏洞/间隙。如果您担心公司间泄漏(不太可能)，您可以通过使用序列作为伪随机生成器的输入来混淆 ID。参见 Daniel Verité 几年前在回答我的问题时写的函数，pseudo_encrypt .

通常出于特定目的，您需要完美连续的无缝 ID，例如发票号。对于那些您需要使用柜台的人 - 是的 - 查找公司 ID。这样的 ID 生成速度很慢并且并发性很差，因此在索引键上使用 JOIN 或两个额外的 SELECT 不会造成太大伤害。不过，不要使用 SELECT 递归地向上架构，只需使用一系列 JOIN。例如，对于 workorder 中的插入，workorder 上的 key 生成触发器类似于(未测试):

   CREATE OR REPLACE FUNCTION workorder_id_tgfn() RETURNS trigger AS $$
   BEGIN
       IF tg_op = 'INSERT' THEN
           -- Get a new ID, locking the row so no other transaction can add a
           -- workorder until this one commits or rolls back.
           UPDATE workorder_ids
           SET next_workorder_id = next_workorder_id + 1 
           WHERE company_id = (SELECT company_id
                 FROM customer
                 INNER JOIN customer_group ON (customer.customer_group_id = customer_group.id) 
                 INNER JOIN company ON (customer_group.company_id = company.id)
               WHERE customer.id = NEW.customer_id)
           RETURNING next_workorder_id
           INTO NEW.id;
       END IF;
   END;
   $$ LANGUAGE 'plpgsql';

对于 UPDATE ... RETURNING ... INTO 语法，请参阅 Executing a Query with a Single-Row Result .

即使不存在多公司问题，正常序列中也可能存在间隙。观察:

CREATE TABLE demo (id serial primary key, blah text);

BEGIN;
INSERT INTO demo(blah) values ('aa');
COMMIT;

BEGIN;
INSERT INTO demo(blah) values ('bb');
ROLLBACK;

BEGIN;
INSERT INTO demo(blah) values ('aa');
COMMIT;

SELECT * FROM demo;

结果:

regress=#     SELECT * FROM demo;
 id | blah 
----+------
  1 | aa
  3 | aa