Tables and Relationships

Only Three Shapes Exist

 every domain reduces to combinations of:

 1:N  (one-to-many)    rider → trips          the workhorse
 N:M  (many-to-many)   trips ↔ drivers        needs a join table
 1:1  (one-to-one)     user → driver_profile  usually = merge or split

 naming them correctly is half of schema design.

One-to-Many

 parent has many children; child belongs to one parent.

 riders                    trips
 ──────                    ──────────────────────
 u-77                      trip_id, rider_id ──► FK to riders

 FOREIGN KEY on the MANY side:
 CREATE TABLE trips (
   rider_id UUID NOT NULL REFERENCES riders(id)
 );
 
 query patterns:
 - all trips for rider      WHERE rider_id = ?     ← index this!
 - rider per trip           JOIN or second lookup

Many-to-Many

 neither side owns the other; the JOIN TABLE is the entity:

 drivers              trip_assignments             trips
 ───────             ───────────────────          ───────
 d-9                 trip_id │ driver_id          t-1001
                     t-1001  │ d-9
                     t-1007  │ d-9

 CREATE TABLE trip_assignments (
   trip_id   UUID REFERENCES trips,
   driver_id UUID REFERENCES drivers,
   assigned_at TIMESTAMPTZ DEFAULT now(),
   PRIMARY KEY (trip_id)          ← business rule encoded:
 );                                  one driver per trip!

 the join table often GROWS UP into a real entity
 (assignment → with state, timestamps) — design it as one early.

One-to-One

 rare as true relationships; usually means something else:

 user ↔ driver_profile (a user MAY be a driver)

 options ranked:
 1. SEPARATE TABLE + unique FK     ← lazy-load rare heavy fields;
                                      keeps users table lean ✓
 2. MERGE into one table           ← nullable columns; fine when
                                      always accessed together
 3. same table, self-FK            ← rarely clearer
 
 signal for splitting: different ACCESS PATTERNS or sizes,
 not "different concepts" alone.

Relationship Design Checklist

 for each relationship decide:

 □ which side holds the FK?         (many side, always, for 1:N)
 □ nullable or required?            (NOT NULL = must exist)
 □ ON DELETE behavior?              
     CASCADE   delete children too       (dangerous default!)
     RESTRICT  refuse if children exist  (usually right)
     SET NULL  orphan gracefully
 □ indexes on BOTH query directions?
     trips(rider_id) for rider→trips
     assignments(driver_id) for driver→trips
 unindexed FK directions become full scans under load.

Interview Framing

Schema-design beats in interviews are scored on relationship fluency: identify the shapes in the domain out loud (“riders 1:N trips, trips N:M drivers via assignments”), place FKs correctly, index both directions, choose delete behavior deliberately. The join-table-becomes-entity observation marks real modeling experience — mention it when your assignment table starts needing status columns.