简单回答您的问题显然是容易的。但我认为在这种情况下这不是一个好的选择,所以我打算从其他角度来回答。
首先:澄清您的业务需求和声明。
- 一个订单可以拥有多个类别。
一个类别可以涉及多个订单。
一个订单每次只能有一个状态,但可以经历多个状态。
一个状态可以为多个订单使用。
一个订单对应一个文件(可能是账单证明)。
- 一个文件只涉及一个订单。
其次:建议
第三步:设计解决方案
![enter image description here](https://istack.dev59.com/hx6SA.webp)
通常这已经足够让你有一个良好的开端了。但我想再多玩一会儿 :) 所以...
第四步:对所需性能的询问
第五步:物理解决方案建议
最终代码下载!(配上好音乐)
CREATE DATABASE command_system;
CREATE SCHEMA in_prgoress_command;
CREATE SCHEMA archived_command;
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 primary key
, notes varchar(500)
, fileULink varchar (500)
)
TABLESPACE in_progress_command_tbs;
CREATE TABLE archived_command.command
(
id bigint 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');
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');
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
)
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
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 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
)
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
- 不要将任何解决方案视为预言性解决方案:进行基准测试。
order_category
创建一个自增id” - 你是在说哪个表?是order
表吗?那么你会将order_category
和id
一起设置为主键?并且在file
和order_status_update
表中添加额外的列以便可以引用这个复合键?这听起来不像一个好主意。根本不行。 - BergiON INSERT
来操作插入的行(在末尾添加“-n”)。 - Islingre(id, order_category)
上创建一个UNIQUE
约束,这也应该能解决竞争条件问题。 - Islingre