基于表列的多个自动增量ids
我需要数据库设计方面的帮助。
我有以下几张表。
伪码：
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类电子产品和玩具，那么我将需要电子产品-1，玩具-1，玩具-2，电子-2，电子-3，玩具-3，玩具-4，玩具-5值与行的order表相关联。但是，不可能根据每个新行(而不是列类型)自动递增增量。
换句话说，对于表order，而不是
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_status表之前，我必须查询2次数据库才能按名称获取order_category和order。稍后，它将是用于查询order表的多个连接。
--
如果有帮助，我将使用后端的烧瓶-sqlalchemy和postgresql作为数据库服务器。
Stack Overflow用户
发布于 2019-11-15 23:13:08
要回答您的侧问题：是的，您应该保留包含名称的表，原因有很多。首先，这类表很小，通常由数据库保存在内存中，因此不使用这些表的性能好处是微不足道的。其次，您希望能够使用外部工具查询数据库和生成报表，并且希望这些工具可以使用这些标签。第三，您希望尽量减少软件与实际数据的耦合，以便它们能够独立发展。添加一个新类别不需要修改您的软件。
现在，关于的主要问题，，没有内置的设施来实现你想要的那种自动增量。你必须自己建造它。
我建议您将每个类别的序列号作为一个列保留在“类别”表中。然后您可以更新它，并在order表像这样 (特定于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;您只需插入类别ID，在示例中有1，它将为该类别创建新的pretty_id。第一类将是玩具-1，下一个玩具-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 |https://stackoverflow.com/questions/58842833
复制相似问题
腾讯云开发者
