Postgresql – the proper way to relate many to many

data integritydatabase-designjoin;many-to-manypostgresql

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in one table for data integrity and UX purposes. I have a shipment table and I want to join a personal_data.

What I'm thinking is:

shipment has one shipper_information which has one personal_data
shipment has one reciever_information which has one personal_data
shipment has one notify_information which has one personal_data

The thought behind this is it would be easier to separate the specific role in the shipment process.

Or would to be a better idea to relate these in a many to many join table and separate the specific role (shipper, reciever, notify) in the front end?

i.e.shipment has many shipment_personal_data which has many personal_data?

Best Answer

If the model is stable and there are always just these three references from shipment to personal_data, plain columns with FK constraints are the right and most efficient way to do it. Something like:

CREATE TABLE shipment (
  shipment_id serial PRIMARY KEY
, shipper_id  int NOT NULL REFERENCES personal_data
, receiver_id int NOT NULL REFERENCES personal_data
, notify_id   int NOT NULL REFERENCES personal_data
, ... -- more columns
);

NOT NULL may or may not fit your requirements.

Related:

A junction table like you considered in closing could make sense for a varying number of references and/or references of dynamic nature (some types of references may be added / removed dynamically). More flexible, but takes up more storage and leads to more complicated queries.

Related Question