How to ensure that a referenced record is unique across a subset of rows defined by a referenced record from another table?

Question

Example situation

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 :

1. 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.
2. 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);

Keeping score

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.

Actual question

• 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 !

Answer 1

Make user_id be part of the primary key of orders and shipments , that is, make the composite keys compulsory.

now you don't have unnecessary duplicated data. It's duplicated, but it's also necessary.

CREATE TABLE users(
  id serial primary key
);
CREATE TABLE orders(
  id serial,
  user_id int not null references users(id),
  primary key (user_id,id)
);
CREATE TABLE shipments(
  id serial,
  user_id int not null references users(id),
  primary key (user_id,id)
);
CREATE TABLE items(
  id serial primary key,
  user_id int not null references users(id),
  order_id int not null,
  shipment_id int,
  foreign key (user_id,order_id)  references orders(user_id,id),
  foreign key (user_id,shipment_id)  references shipments(user_id,id)
);

Answer 2

Not sure whether to answer this or not because I don't know for sure the bits are supported, but here's how I would approach it:

1. Materialise a view 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
2. Put a constraint on ABC.users_per_shipment in(0,1); -- or maybe just =1

Not elegant and not obvious but it would keep data integrety by preventing shipments crossing users via that constraint (provided Postgresql supports such things).

I'd probably also model shipments as a new table item_shipment(item_id, shipment_id). Not relevant to the problem here but you'd manage it in a similar way.