MEPX
Chapter 4 of 10All chapters
  1. 1Why a database
  2. 2Tables, rows and types
  3. 3Keys
  4. 4Relationships
  5. 5Normalisation
  6. 6Transactions
  7. 7Indexes
  8. 8When not to use tables
  9. 9Changing the schema
  10. 10Backups and growth

Chapter 4 of 10

Relationships

One to one, one to many, many to many.

The three shapes

One to many is the common case: one customer, many orders, with the key stored on the many side. Many to many needs a third table holding pairs of keys.

  • The join table is the natural home for facts about the pairing, such as a date or a role.
  • A list of ids stuffed into one column cannot be joined or constrained.

Cardinality is a requirement

Whether a person can have two addresses is a business question, not a technical one. Ask it before designing, because changing it later rewrites queries everywhere.