Designing a Table to Live With
There Is No Column That Can Hold a List
Last timeChoosing What Identifies a Row
Two of the three relationship shapes are a column. The third cannot be, and the reason is worth deriving rather than memorising, because it tells you what the extra table is.
Each table from the previous lessons has a key. This lesson is about how tables
point at each other, which comes in exactly three shapes, two of which are
trivial. The third is the one worth deriving.
The shape that is just a column
One order has many lines. One author writes many books, in the simple version
where books have one author. One department contains many employees.
The relationship is a single column, and the only real question is where it
lives.
SHAPE ONE: one to many
orders id, placed_on, customer_id
order_lines id, order_id, product_id, quantity
The line carries the order. One column, on the many side.
SHAPE TWO: one to one
accounts id, email
account_settings account_id, theme, timezone
The same as above with a uniqueness constraint on the
reference, so one account has at most one settings row.
Mostly used to split rarely-read columns off a hot table.
SHAPE THREE: many to many
students id, name
courses id, title
enrolments student_id, course_id, enrolled_on, grade
Neither of the first two tables can hold the relationship.
The third table holds it, one row per pairing, and the key
of that table is the two columns together.Which side holds it
The rule is one sentence and it is worth deriving rather than remembering. Put
the reference on the side where the answer is always a single value.
An employee belongs to one department, so a department reference fits in one
cell on the employee row. A department has many employees, so an employee
reference does not fit in one cell on the department row. There is no column
type that holds a list and still behaves like a column.
The lesson stops here
2 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents