Three shapes, and only three. Getting a relationship right is almost always a question of which table holds the foreign key — and for one of the three shapes, the honest answer is "neither".
2a
The brief already told you the cardinality. Walk it with Next — each sentence becomes a relationship, and two of them have a tempting wrong version first.
patient_id lives on appointments — one value per row, which is the only shape a column can hold.UNIQUE on it. Put the key on the optional side, then make it unique — that is the entire difference.Read the sentence in both directions. "A doctor has specialities" sounds one-to-many until you ask the other way: does a speciality have many doctors? It does — and that second reading is what forces the join table.
2b
A patient has exactly one medical record. That is real — but it is also the relationship people reach for when they should have added columns.
CREATE TABLE medical_records ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, patient_id integer NOT NULL UNIQUE REFERENCES patients(id) ON DELETE CASCADE, blood_type text, allergies text[], notes text ); -- UNIQUE is what turns one-to-many into one-to-one. -- Drop it and a patient silently acquires a second record.
patient_details table because the main one "looks long" buys you a mandatory join on every read and no guarantee the row exists.ON DELETE CASCADE is defensible here precisely because the record cannot exist without its patient. That is rarely true for the other two shapes.Putting the foreign key on the parent — patients.medical_record_id. It works, but now a patient row cannot be created until its record exists, the column is null for most rows, and nothing stops two patients pointing at the same record. Key on the dependent side, always.
2c
A doctor has many specialities; a speciality has many doctors. No column on either table can hold that, so the link gets a table of its own.
CREATE TABLE specialities ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE ); CREATE TABLE doctor_specialities ( doctor_id integer NOT NULL REFERENCES doctors(id) ON DELETE CASCADE, speciality_id integer NOT NULL REFERENCES specialities(id) ON DELETE RESTRICT, certified_on date, -- the join table can carry its own data PRIMARY KEY (doctor_id, speciality_id) -- composite: one pairing, once );
PRIMARY KEY (doctor_id, speciality_id) makes the same pairing impossible twice — without it, "cardiology" can be added to the same doctor forty times.certified_on belongs to the pairing, not to the doctor and not to the speciality. The moment you notice a field like that, the join table has become an entity in its own right.ON DELETE actions differ on purpose. Deleting a doctor should remove their pairings (CASCADE); deleting a speciality that doctors still hold should fail (RESTRICT).specialities text holding 'cardiology,neurology' cannot be joined, indexed, constrained or counted. It is the single most common modelling mistake, and Session 03 shows exactly why it fails.The clinic adds rooms. (a) An appointment happens in exactly one room, and a room hosts many appointments — write that relationship and say which table gets the foreign key. (b) A room can be equipped for several procedures, and a procedure can happen in several rooms — write that one too, including the composite primary key. (c) One of your two join columns wants an ON DELETE CASCADE and one wants RESTRICT. Decide which, and defend it in a sentence.
Recap
UNIQUE, keyed on the dependent side — and is worth it only for different lifetimes, permissions or sparsity.