示例情况
让我们想象一下以下情况:用户可以下订单。订单由项目组成。货物被发送给用户。发货由物品组成。
为了优化供应链,允许将来自不同订单的物品包装在一个共同的装运中,前提是这些订单都与同一用户相关联
我很难理解对情况进行建模的最佳方法是什么,以优雅地适应上一段中斜体所示的约束,而无需复制数据或到处进行检查。
我的第一直觉
通过零重复数据,我可以轻松得出以下模式:
CREATE TABLE users(
id serial primary key
)
CREATE TABLE orders(
id serial primary key,
user_id int not null references users
)
CREATE TABLE shipments(
id serial primary key
)
CREATE TABLE items(
id serial primary key,
order_id int not null references orders,
shipment_id int references shipments
)
虽然它确实实现了订单和商品以及发货和商品之间的 1-N 关系,但这并不妨碍执行以下操作:
INSERT INTO orders(id,user_id) VALUES (1,1);
INSERT INTO orders(id,user_id) VALUES (2,2);
INSERT INTO shipments(id) VALUES (1)
INSERT INTO items(order_id,shipment_id) VALUES (1,1);
INSERT INTO items(order_id,shipment_id) VALUES(2,1);
这会导致货件 #1 具有与不同用户相关联的两个项目(通过他们各自的订单)的情况。
我找到了解决我的问题的两种解决方案,两种方法都有效,但对我来说似乎都不行:
- 编写一个用户定义的函数,如果添加商品不会违反每批货一个用户的规则,则该函数对于给定的 item_id 和shipment_id 返回 true,并添加调用此函数的检查约束。它看起来确实像是一种滑坡,你会失去 SQL 的良好声明性方法而进入某种命令式回调地狱,我本能地不喜欢它。
- 复制商品表和发货表上的 user_id,并在商品表上写入两个引用订单的复合外键,确保不会违反每次发货一个用户的规则。现在我们复制了数据,我现在还必须注意迄今为止隐含的“每订单一个用户”规则:
ALTER TABLE items ADD user_id int not null references users;
ALTER TABLE shipments ADD user_id int not null references users;
ALTER TABLE orders ADD UNIQUE (id,user_id); -- For some reason postgres wants this ?
ALTER TABLE shipments ADD UNIQUE (id, user_id);
ALTER TABLE items ADD FOREIGN KEY (order_id,user_id) references orders(id,user_id);
ALTER TABLE items ADD FOREIGN KEY (shipment_id,user_id) references shipments(id,user_id);
记分
解决方案 1 保持了干净的架构,但引入了程序复杂性。我还听说标量用户定义函数在性能方面不是很好。
解决方案 2 对我来说绝对感觉更强大,但我不喜欢我重复(如果我诚实的话,是三倍) user_id 数据和添加的要求。
实际问题
- 执行我想做的事情的首选做法是什么,即确保引用记录(通过订单的用户)在另一个表中的另一个引用记录(发货)定义的行子集中是唯一的?
- 如果没有明显更好的解决方案,还有哪些其他可能的解决方案?
- 我所描述的两种方法还有哪些我肯定忽略的其他优点和缺点?
- 我面临的问题有一个我现在可以使用的名称或术语吗?
这看起来是一个简单的情况,我认为更有经验的 RDBMS 用户会发现这微不足道......但我很遗憾地报告,我在关系数据库方面非常缺乏经验,我怀疑我无法找到我的答案问题本质上来自于我无法用正确的术语表达它......我很尴尬地问这样一个愚蠢的问题,这可能是重复的,但我的谷歌这次失败了!
使成为和
user_id
的主键的一部分 ,即使组合键成为强制键。orders
shipments
现在你没有不必要的重复数据了。它是重复的,但也是必要的。
不确定是否回答这个问题,因为我不确定这些位是否受支持,但我的处理方法如下:
ABC select i.shipment_id, count(distinct o.user id) as users_per_shipment from items i join orders o on(o.order_id=i.order_id) group by i.shipment_id
ABC.users_per_shipment in(0,1); -- or maybe just =1
不优雅也不明显,但它可以通过防止通过该约束跨越用户的运输来保持数据完整性(前提是 Postgresql 支持此类事情)。
我可能还会将发货建模为新表
item_shipment(item_id, shipment_id)
。与这里的问题无关,但您可以以类似的方式进行管理。