基于表列的多个自增id

Question

我需要数据库设计方面的帮助。

我有以下表格。

伪代码：

Table order_status {
  id int[pk, increment]
  name varchar
}

Table order_status_update {
  id int[pk, increment]
  order_id int[ref: > order.id]
  order_status_id int[ref: > order_status.id]
  updated_at datetime
}

Table order_category {
  id int[pk, increment]
  name varchar
}

Table file {
  id int[pk, increment]
  order_id int[ref: > order.id]
  key varchar
  name varchar
  path varchar 
}

Table order {
  id int [pk] // primary key
  order_status_id int [ref: > order_status.id]
  order_category_id int [ref: > order_category.id]
  notes varchar
  attributes json  // no of attributes is not fixed, hence needed a json column
}

每个order_category_id列都需要一个自动递增的id，之前一切正常。例如，如果我有2个类别：电子和玩具，那么我需要将electronics-1、toy-1、toy-2、electronics-2、electronics-3、toy-3、toy-4、toy-5值与order表中的行相关联。但是这不可能，因为自动递增是基于每一行而不是列类型的。

换句话说，对于order表，我们需要在每个order_category_id类型中实现自动递增。

  id  order_category_id
---------------------
  1       1       
  2       1       
  3       1     
  4       2       
  5       1      
  6       2
  7       1

我需要以下内容：

 id  order_category_id pretty_ids
----------------------------
  1       1       toy-1
  2       1       toy-2
  3       1       toy-3
  4       2       electronics-1
  5       1       toy-4
  6       2       electronics-2
  7       1       toy-5

我尝试的方法：

我为每个订单类别创建了单独的表格（虽然这不是一个理想的解决方案，但目前我有6个订单类别，所以这样做可以暂时解决问题）。

现在，我有了electronics_order和toys_order的表格。列是重复的，但它能正常工作。但现在我又遇到了另一个问题，我的每个与其他表格的关系都被破坏了。由于electronics_order和toys_orders都可以拥有相同的id，我不能使用id列来引用order_status_update、order_status、 file表格。 我可以在每个这些表格中创建另一个列order_category，但这是否是正确的方式？我对数据库设计没有经验，所以我想知道别人是怎么做的。

我还有一个旁询。

我需要为order_category和order_status创建表格来存储名称吗？因为这些值不会经常更改，我可以将它们存储在代码中并保存在order表格的列中。

我知道单独的表格对于灵活性很好，但在将新行插入order表格之前，我必须查询数据库两次才能获取名称为order_status和order_category的值。而且以后在查询order表格时需要进行多个连接。

--

如果有帮助的话，我在后端使用了flask-sqlalchemy，并使用postgresql作为数据库服务器。

- Jashwant

---

“现在我需要为每个order_category创建一个自增id” - 你是在说哪个表？是order表吗？那么你会将order_category和id一起设置为主键？并且在file和order_status_update表中添加额外的列以便可以引用这个复合键？这听起来不像一个好主意。根本不行。 - Bergi

或者你可以使用一个触发器 ON INSERT 来操作插入的行（在末尾添加“-n”）。 - Islingre

@Jashwant 我猜你可能需要序列化它...但如果你还在(id, order_category)上创建一个UNIQUE约束，这也应该能解决竞争条件问题。 - Islingre

@Jashwant 是的，它就像一个连接。我认为这样做没有任何问题。与直接在“order”表中存储字符串值相比，它的好处是减少磁盘空间并能够轻松更改名称。不确定你所说的“在代码中存储名称”是什么意思。 - Bergi

这可以通过在select语句中使用partition by和row_number()轻松实现，但将其存储为表的一部分则更加棘手。我已经回答了一个类似于SQL Server的问题，但我不知道如何在PostgreSQL上实现。希望这有所帮助。 - Zohar Peled

Answer 1

为了追踪基于订单类别的增量ID，我们可以在另一张表上跟踪这个值。让我们称其为：`order_category_sequence`表。为了展示我的解决方案，我刚刚创建了一个简化版本的带有`order_category`的`order`表。

CREATE TABLE order_category (
  id SERIAL PRIMARY KEY,
  name  VARCHAR(100) NULL
); 


CREATE TABLE order_category_sequence (
  id SERIAL PRIMARY KEY,
  order_category_id int NOT NULL,
  current_key  int not null
);

Alter Table order_category_sequence Add Constraint "fk_order_category_id" FOREIGN KEY (order_category_id) REFERENCES order_category (id);
Alter Table order_category_sequence Add Constraint "uc_order_category_id" UNIQUE (order_category_id);


CREATE TABLE "order" (
  id SERIAL PRIMARY KEY,
  order_category_id int NOT NULL,
  pretty_id  VARCHAR(100)  null
);

Alter Table "order" Add Constraint "fk_order_category_id" FOREIGN KEY (order_category_id) REFERENCES order_category (id);

order_category_sequence表中的order_category_id列指向order_category。current_key列保存order中的最后一个值。

当添加新的订单行时，我们可以使用触发器从order_category_sequence读取最后一个值并更新pretty_id。以下触发器定义可用于实现此目的。

--function called everytime a new order is added
CREATE OR REPLACE FUNCTION on_order_created()
  RETURNS trigger AS
$BODY$

DECLARE 
current_pretty_id varchar(100);

BEGIN

-- increment last value of the corresponding order_category_id in the sequence table
Update order_category_sequence
set current_key = (current_key + 1)
where order_category_id = NEW.order_category_id;

--prepare the pretty_id
Select 
oc.name || '-' || s.current_key AS   current_pretty_id 
FROM    order_category_sequence AS s
JOIN order_category AS oc on s.order_category_id = oc.id
WHERE s.order_category_id = NEW.order_category_id
INTO current_pretty_id;

--update order table
Update "order"
set pretty_id = current_pretty_id
where id = NEW.id;


RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER order_created
  AFTER INSERT
  ON "order"
  FOR EACH ROW
  EXECUTE PROCEDURE on_order_created();

如果我们想要同步两个表，order_category 和 order_category_sequence，我们可以使用另一个触发器，在每次添加新的订单类别时在后者表中创建一行。

//function called everytime a new order_category is added
CREATE OR REPLACE FUNCTION on_order_category_created()
  RETURNS trigger AS
$BODY$

BEGIN
--insert a new row for the newly inserted order_category
Insert into order_category_sequence(order_category_id, current_key)
values (NEW.id, 0);

RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER order_category_created
  AFTER INSERT
  ON order_category
  FOR EACH ROW
  EXECUTE PROCEDURE on_order_category_created();

测试查询和结果：

Insert into order_category(name)
values ('electronics'),('toys');

Insert into "order"(order_category_id)
values (1),(2),(2);


select * from "order";

关于你的附加问题，我更喜欢将订单状态和订单类别等查找值存储在单独的表中。这样做可以获得上述灵活性，并且当我们需要进行更改时也很容易。

- Yared

---

我会称之为“子类”，因为玩具有多个子类别。 - HackSlash

Answer 2

回答你的“旁边问题”：是的，你应该保留包含名称的表，有很多原因。首先，这些表很小，通常由数据库保存在内存中，因此不使用这些表几乎没有性能优势。其次，您希望能够使用外部工具查询数据库并生成报表，并且您希望这些标签对这些工具可用。第三，您希望最小化软件与实际数据之间的耦合，以便它们可以独立演变。添加新类别不应要求修改您的软件。

现在，对于主要问题，没有内置的自动递增功能满足您的需求。您必须自己构建它。

我建议您将每个类别的序列号作为类别表中的一列保留下来。然后，您可以更新它并在订单表中使用更新后的序列号，像这样（这仅适用于PostgreSQL）。

-- set up the tables

create table orders (
  id SERIAL PRIMARY KEY,
  order_category_id int,
  pretty_id VARCHAR
);
create unique index order_category_pretty_id_idx 
  on orders (pretty_id);

create table order_category (
  id SERIAL PRIMARY KEY,
  name varchar NOT NULL,
  seq int NOT NULL default 0
);

-- create the categories
insert into order_category
(name) VALUES
('toy'), ('electronics');


-- create orders, specifying the category ID and generating the pretty ID

WITH 
  new_category_id (id) AS (VALUES (1)), -- 1 here is the category ID for the new order
  pretty AS (
    UPDATE order_category 
    SET seq = seq + 1
    WHERE id = (SELECT id FROM new_category_id)
    RETURNING *
  )
INSERT into orders (order_category_id, pretty_id)
SELECT new_category_id.id, concat(pretty.name, '-', pretty.seq) 
FROM new_category_id, pretty;

你只需要在示例中的 1 处插入你的类别 ID，它就会为该类别创建新的 pretty_id。第一个类别将是 toy-1，下一个将是 toy-2，以此类推。

| id  | order_category_id | pretty_id     |
| --- | ----------------- | ------------- |
| 1   | 1                 | toy-1         |
| 2   | 1                 | toy-2         |
| 3   | 2                 | electronics-1 |
| 4   | 1                 | toy-3         |
| 5   | 2                 | electronics-2 |

- Old Pro

---

我想要将toy-1和toy-2与“order”表的行关联而不是与“order_category”表关联。我已经更新了我的问题并添加了“pretty_ids”列以更好地解释这一点。你的答案仍然一样，我应该存储为toy-1324和toy-9009吗？此外，你能回答我的附加问题吗？ - Jashwant

@Jashwant 我回答了你的附加问题。但我仍然不明白为什么你需要多个版本的“玩具”类别。我也不理解你所做的更改，因为“订单”表应该有“order_category_id”，而不是“order_category”，漂亮的ID应该在“order_category”表中。要么toy-1和toy-2是相同的，那么你可以/应该只使用toy，要么它们是不同的，那么你应该给它们有意义的不同名称，比如toy-mechanical和toy-electronic。 - Old Pro

订单表中的字段名为 order_category_id。现已修复。不同类别的订单在前端显示在不同的页面上。在每个页面上，我需要按顺序显示订单编号，例如 toy-1、toy-2、toy-3、toy-4，而不是 toy-2、toy-5、toy-6（因为主键 1、3、4 已被不同订单类别的订单占用）。漂亮的编号不能在 order_category 表中。order_category 表将有固定数量的行。order 表与 order_category 表之间存在多对一的关系。 - Jashwant

@Jashwant 现在我更好地理解了你想要实现的目标，因此我已经相应地更新了我的答案。我认为我已经完全回答了你的问题和附加问题。如果还有疑问，请告诉我。 - Old Pro

@OldPro 在Postgres中有一个有趣的解决方案，您使用update order_category作为锁定方式，这意味着它确实降低了DB服务器的能力，我会在答案中提到它。 - Vladimir

Answer 3

为了做玩具-1，玩具-2和玩具-3，您应该重复order_status update的逻辑，按时间或数量跟踪某些状态之间没有区别。只是在order_status update中更简单，您只需将now()放入updated_at中，例如对于order_category_track，您将取最后一个值+1或有不同的序列相应类别（不建议这样做，因为它将数据库对象与DB中的数据绑定在一起）。我会更改架构为： 在此模式下可能处于不一致状态。但是，在我看来，在您的应用程序中有三个不同的实体“订单”，“订单状态”和“订单类别跟踪”，它们各自生活。而且，如果没有锁定，几乎不可能实现此任务的一致状态。这个任务的条件很复杂，因为下一行取决于前一行，这与SQL相矛盾。

- Vladimir

Answer 4

我将只关注你展示的3个表：order，order_status和order_category。 为新记录创建一个新表不是正确的方法。根据你的解释，我认为你试图使用order和order_category表作为多对多关系。如果是这样，你需要的是像这样的中间表： 我目前在订单表中添加了order_status列， 你可以根据需要将此列添加到这些表之一。
附带问题： 对于order_status，如果订单状态是固定的（例如仅有ACTIVE，INACTIVE，并且未来不会有更多值），最好使用ENUM类型的列。

- Kyi Moe Min

Answer 5

我建议将类别分为两级层次结构：类别（玩具，电子产品）和子类别（玩具-1，玩具-2，电子产品-1等）：

因此，您可以使用列order_subcategory.full_name包含编译的“玩具-1”值，或者您可以创建视图来即时生成此字段：

select oc.name || "-" || os.number
from order_category as oc 
join order_subcategory as os on oc.id = os.category_id

https://dbdiagram.io/d/5dd6a132edf08a25543e34f8

关于您的问题“我需要为order_category和order_status创建表格来存储名称吗？”： 最佳实践是将这种数据存储为单独的字典表。这样可以确保一致性和可靠性。对这些表进行查询对于关系型数据库管理系统非常快速和容易，因此请放心使用它。

- Alex

---

是的，这是正确的方法，我会将表order_subCategory重命名为Items，order_category => itemCategory。只是这样在阅读时更有意义 :) - Krish

你将在关系型数据库中添加计算数据。我认为这会在未来的演进/维护中给你带来麻烦。我认为模式很好。但是不要在其中放全名 ;) （但如果你使用PG12：也许可以使用生成列。更少的人工控制，更易于维护。） - Jaisus

正如我所提到的，如果更容易使用，这个字段可以在视图中编译。否则，使用存储过程填充表格应该可以解决问题。 - Alex

Answer 6

简单回答您的问题显然是容易的。但我认为在这种情况下这不是一个好的选择，所以我打算从其他角度来回答。

首先：澄清您的业务需求和声明。

• 一个订单可以拥有多个类别。

• 一个类别可以涉及多个订单。

• 一个订单每次只能有一个状态，但可以经历多个状态。

• 一个状态可以为多个订单使用。

• 一个订单对应一个文件（可能是账单证明）。

• 一个文件只涉及一个订单。

其次：建议

• 在生产环境中，有一些保留关键字是不能使用的 (https://www.postgresql.org/docs/current/sql-keywords-appendix.html)。例如，我会用“command”代替“order”这个词。
• 在生产之前必须回答的剩余问题：为什么你的“order”表中有“attributes”属性？这里存在违反正常形式的风险。(https://www.geeksforgeeks.org/normal-forms-in-dbms/)

第三步：设计解决方案

通常这已经足够让你有一个良好的开端了。但我想再多玩一会儿 :) 所以...

第四步：对所需性能的询问

• 估计每天/每月在订单中的负载量（每月一千万行？）

第五步：物理解决方案建议

• 将归档到另一个表空间（当取消或终止时触发 => 归档）
• 索引放在另一个表空间中（您的 DBA 会感谢您的）
• 订单表可能需要分区（https://pgxn.org/dist/pg_partman/doc/pg_partman.html, https://www.postgresql.org/docs/current/ddl-partitioning.html）
• 硬件和选项选择（高可用性？灾难管理？如果是：需要进一步研究但很少）
• 数据转置（真的有必要吗？如果是：需要进一步研究但很少）

最终代码下载！（配上好音乐）

-- as a postgres user
CREATE DATABASE command_system;
CREATE SCHEMA in_prgoress_command;
CREATE SCHEMA archived_command;
--DROP SCHEMA public;
-- create tablespaces on other location than below
CREATE TABLESPACE command_indexes_tbs location 'c:/Data/indexes';
CREATE TABLESPACE archived_command_tbs location 'c:/Data/archive';
CREATE TABLESPACE in_progress_command_tbs location 'c:/Data/command';

CREATE TABLE in_prgoress_command.command
(
    id bigint /*or bigserial if you use a INSERT RETURNING clause*/ primary key
    , notes varchar(500)
    , fileULink varchar (500)
)
TABLESPACE in_progress_command_tbs;

CREATE TABLE archived_command.command
(
    id bigint /*or bigserial if you use a INSERT RETURNING clause*/ primary key
    , notes varchar(500)
    , fileULink varchar (500)
)
TABLESPACE archived_command_tbs;

CREATE TABLE in_prgoress_command.category
(
    id int primary key
    , designation varchar(45) NOT NULL
)
TABLESPACE in_progress_command_tbs;
INSERT INTO in_prgoress_command.category 
VALUES (1,'Toy'), (2,'Electronic'), (3,'Leather'); --non-exaustive list

CREATE TABLE in_prgoress_command.status
(
    id int primary key
    , designation varchar (45) NOT NULL
)
TABLESPACE in_progress_command_tbs;

INSERT INTO in_prgoress_command.status 
VALUES (1,'Shipping'), (2,'Cancel'), (3,'Terminated'), (4,'Payed'), (5,'Initialised'); --non-exaustive list

CREATE TABLE in_prgoress_command.command_category
(
    id bigserial primary key
    , idCategory int 
    , idCommand bigint
)
TABLESPACE in_progress_command_tbs;

ALTER TABLE in_prgoress_command.command_category
ADD CONSTRAINT fk_command_category_category FOREIGN KEY (idCategory) REFERENCES in_prgoress_command.category(id);

ALTER TABLE in_prgoress_command.command_category
ADD CONSTRAINT fk_command_category_command FOREIGN KEY (idCommand) REFERENCES in_prgoress_command.command(id);

CREATE INDEX idx_command_category_category ON in_prgoress_command.command_category USING BTREE (idCategory) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_category_command ON in_prgoress_command.command_category USING BTREE (idCommand) TABLESPACE command_indexes_tbs;

CREATE TABLE archived_command.command_category
(
    id bigserial primary key
    , idCategory int 
    , idCommand bigint
)
TABLESPACE archived_command_tbs;

ALTER TABLE archived_command.command_category
ADD CONSTRAINT fk_command_category_category FOREIGN KEY (idCategory) REFERENCES in_prgoress_command.category(id);

ALTER TABLE archived_command.command_category
ADD CONSTRAINT fk_command_category_command FOREIGN KEY (idCommand) REFERENCES archived_command.command(id);

CREATE INDEX idx_command_category_category ON archived_command.command_category USING BTREE (idCategory) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_category_command ON archived_command.command_category USING BTREE (idCommand) TABLESPACE command_indexes_tbs;

CREATE TABLE in_prgoress_command.command_status
(
    id bigserial primary key
    , idStatus int 
    , idCommand bigint
    , change_timestamp timestamp --anticipate if you can the time-zone problematic
)
TABLESPACE in_progress_command_tbs;

ALTER TABLE in_prgoress_command.command_status
ADD CONSTRAINT fk_command_status_status FOREIGN KEY (idStatus) REFERENCES in_prgoress_command.status(id);

ALTER TABLE in_prgoress_command.command_status
ADD CONSTRAINT fk_command_status_command FOREIGN KEY (idCommand) REFERENCES in_prgoress_command.command(id);

CREATE INDEX idx_command_status_status ON in_prgoress_command.command_status USING BTREE (idStatus) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_status_command ON in_prgoress_command.command_status USING BTREE (idCommand) TABLESPACE command_indexes_tbs;
CREATE UNIQUE INDEX idxu_command_state ON in_prgoress_command.command_status USING BTREE (change_timestamp, idStatus, idCommand) TABLESPACE command_indexes_tbs;

CREATE OR REPLACE FUNCTION sp_trg_archiving_command ()
    RETURNS TRIGGER
language plpgsql
as $function$
DECLARE
BEGIN
    -- Copy the data
    INSERT INTO archived_command.command
    SELECT *
    FROM in_prgoress_command.command
    WHERE new.idCommand = idCommand;    

    INSERT INTO archived_command.command_status (idStatus, idCommand, change_timestamp)
    SELECT idStatus, idCommand, change_timestamp
    FROM in_prgoress_command.command_status
    WHERE idCommand = new.idCommand;    

    INSERT INTO archived_command.command_category (idCategory, idCommand)
    SELECT idCategory, idCommand
    FROM in_prgoress_command.command_category
    WHERE idCommand = new.idCommand;    

    -- Delete the data
    DELETE FROM in_prgoress_command.command_status
    WHERE idCommand = new.idCommand;    
    DELETE FROM in_prgoress_command.command_category
    WHERE idCommand = new.idCommand;    
    DELETE FROM in_prgoress_command.command
    WHERE idCommand = new.idCommand;    
END;
$function$;

DROP TRIGGER IF EXISTS t_trg_archiving_command ON in_prgoress_command.command_status;
CREATE TRIGGER t_trg_archiving_command
AFTER INSERT
ON in_prgoress_command.command_status
FOR EACH ROW
WHEN (new.idstatus = 2 or new.idStatus = 3)
EXECUTE PROCEDURE sp_trg_archiving_command();

CREATE TABLE archived_command.command_status
(
    id bigserial primary key
    , idStatus int 
    , idCommand bigint
    , change_timestamp timestamp --anticipate if you can the time-zone problematic
)
TABLESPACE archived_command_tbs;

ALTER TABLE archived_command.command_status
ADD CONSTRAINT fk_command_command_status FOREIGN KEY (idStatus) REFERENCES in_prgoress_command.category(id);

ALTER TABLE archived_command.command_status
ADD CONSTRAINT fk_command_command_status FOREIGN KEY (idCommand) REFERENCES archived_command.command(id);

CREATE INDEX idx_command_status_status ON archived_command.command_status USING BTREE (idStatus) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_status_command ON archived_command.command_status USING BTREE (idCommand) TABLESPACE command_indexes_tbs;
CREATE UNIQUE INDEX idxu_command_state ON archived_command.command_status USING BTREE (change_timestamp, idStatus, idCommand) TABLESPACE command_indexes_tbs;

结论：

• 在许多情况下，当您担心键的排列时，这是因为它们不在正确的位置。汽车钥匙也是如此！ :D
• 不要将任何解决方案视为预言性解决方案：进行基准测试。

- Jaisus

---

客户经常在订单表单中添加/删除字段。这些字段需要保存在订单表中。这些字段不是所有订单类别都通用的，有些有它们，有些没有。我不能一次又一次地增加表格中的列。因此，我将它们保存在一个JSON列中。它没有模式，因此对我很有效。另一个解决方案是为这些字段创建一个表，并将字段名称和订单ID传递给它，但这是postgresql，为什么不使用JSON？ - Jashwant

关系型数据库管理系统中的第一条规则之一是在数据库中具有原子数据。但是，如果这是客户端可以添加列的数据，我想你别无选择;)（很多人将任何内容放入json列中而不考虑正常形式。这就是为什么我提出了这个备注的原因;） - Jaisus