Let's imagine the following situation : A user can place orders. Orders are composed of items. Shipments are sent to the user. Shipments are comprised of items.
In order to optimize the supply chain, it is allowed that items from different orders are packed in a common shipment, provided that the orders are all associated with the same user
I have trouble understanding what is the best way to model the situation to gracefully accommodate the constraint that is italicized in the previous paragraph without either duplicating data or resorting to checks all over the place.
My first intuition
With zero duplication of data, I can easily come up with the following schema :
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 )
Although it does implement the 1-N relationships between orders and items and shipments and items I desire, this does not prevent the following to be done :
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);
Which yields the situation of shipment #1 having two items associated with different users (through their respective orders).
I found two solutions to my problems, both of which work and neither of which seem OK to me :
- Write a User-defined function that returns true for a given item_id and shipment_id if adding the item would not violate the one-user-per-shipment rule, and add a check constraint calling this function. It does look like a sort of slippery slope where you lose the nice declarative approach of SQL to enter in some sort of imperative callback hell, and I instinctively dislike it.
- Duplicate the user_id on the items and shipments tables, and write two composite foreign keys on the items table referencing orders ensuring one can't violate the one-user-per-shipment rule. Now that we duplicated the data, I also now have to pay attention to the hitherto implicit one-user-per-order rule:
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);
Solution 1 keeps a clean schema, but introduces procedural complexity. Also I hear scalar User-defined functions are not very good performance-wise.
Solution 2 definitely feels more robust to me, but I don't like that I duplicated (tripled if I'm honest) the user_id data and the added requirement.
- What is the preferred practice to do what I am trying to do, which is to ensure that a referenced record (users through orders) is unique across in the subset of rows defined by another referenced record (shipments) from another table ?
- If there is no clearly better solution, what are other possible solutions ?
- What are the other pros and cons of the two approaches I described which I have most certainly overlooked ?
- Has the problem I am facing got a name or jargon that I can now use ?
This looks like a simple situation and I think the more experienced users of RDBMSs will find this trivial... But I am sorry to report that I am very inexperienced when it comes to relational databases and I suspect me failing to find an answer for my problems comes essentially from my inability to articulate it in the proper terms... I am embarrassed to ask such a stupid question which is probably a duplicate, but my google-fu failed me this time !