Cardinality, optionality and bridge tables
Part 1 of 1
Read both ends of a relationship
A customer can exist before making a booking. Every booking belongs to exactly one customer. State each direction separately: a customer has zero or many bookings; a booking has exactly one customer.
Put customer_id on bookings, the many side. NOT NULL follows “exactly one”; REFERENCES preserves the relationship to customers(id). Data observed today does not establish all future business rules.
A booking can include several kayaks, and a kayak can appear in many bookings over time. Resolve that many-to-many relationship using booking_items, with one row for each booking–kayak pair.
Worked example
customers: zero to many bookings. bookings: exactly one customer. bookings.customer_id is a required FK. booking_items can use PRIMARY KEY (booking_id, kayak_id).
Pause and explain. Explain the minimum at both ends using the client’s words, then sketch the crow’s-foot relationship.