Designing a database
| English | Chinese | Pinyin |
|---|---|---|
| tables | 表 | biǎo |
| relational database | 关系数据库 | guān xì shù jù kù |
| entities | 实体 | shí tǐ |
| primary key | 主键 | zhǔ jiàn |
| foreign key | 外键 | wài jiàn |
| normalisation | 规范化 | guī fàn huà |
Design the tables before you write a query
- Almost every problem later is a table problem wearing a query's clothes.
- A relational database 关系数据库 stores data in tables 表 of rows and columns.
- Design by asking what the entities 实体 are — the things you store — and then what connects them.
Keys
- A primary key 主键 identifies a row uniquely. Every table needs one.
- A foreign key 外键 points at another table's primary key, and that pointer is the relationship.
- Keys are how a database keeps two tables consistent with each other, which is work you would otherwise do by hand and get wrong.
Which makes the worst primary key?
It changes, and people share them. A primary key should have no job except identifying the row.
What is the name for a column that points at another table's primary key?
That pointer is the relationship, and it is what keeps the two tables consistent.
Normalisation
- Normalisation 规范化 means one fact lives in exactly one place.
- If a course title is stored beside every enrolment, renaming the course means finding every copy — and missing one is now a permanent inconsistency.
- Store the title once in
courses, and point at it. That is the entire idea.
Storing the course title beside every enrolment saves a join and is good design.
Renaming the course now means finding every copy, and missing one is a permanent inconsistency.
A school stores students, courses, and who takes what.
Two tables cannot express it: a student takes many courses, and a course has many students.
The relationship needs a third table — enrolments — whose rows are (student, course) pairs, each a foreign key.
Recognising that a many-to-many relationship needs its own table is the single most useful database idea in the module, and it is where most first designs go wrong.
Students take many courses and courses have many students. How many tables?
A many-to-many relationship cannot live in either table. Its rows are (student, course) pairs.
A library lends books to members. Name the tables you would create and say why.
Books, members, and loans. The loan is an event connecting the two, with its own dates.
One fact, one place. Every duplication you allow is a future inconsistency you have scheduled. When you find yourself typing the same value into two tables, the design is telling you something.
Do not use a person's name, an email address or a phone number as a primary key. All of them change, and all of them repeat. Give every table an id whose only job is to identify the row.