Normalisation
Databases & SQL Lesson 17 2:30 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Put every club booking in one table and something goes wrong quietly.
把每一条社团报名都放进一张表里，就会有东西悄悄出问题。
Look at the coach column: Mr Ng is written twice over, once for each Chess booking.
看那个"教练"列：Mr Ng 被写了两遍，每一条象棋社的报名各一遍。
If he leaves, two rows must be changed together.
如果他离职了，两行必须一起改。
Miss one, and the database now contradicts itself about who coaches Chess — and nothing warns you.
漏掉一行，这个数据库现在就对"谁教象棋"这件事自相矛盾—— 而且没有任何东西会警告你。
Normalisation is the process of organising tables so that cannot happen, and it goes in steps.
规范化就是把表组织成让那种事不会发生的过程，而它是分步进行的。
First normal form: one value per cell — no lists, no repeating groups — and the table has a primary key.
第一范式：每个单元格只装一个值——没有列表，没有重复组—— 并且这张表有一个主键。
Second: no column depends on only part of the key.
第二：没有任何一列只依赖于键的一部分。
Third: no column depends on another non-key column.
第三：没有任何一列依赖于另一个非键列。
Each step assumes the one before it.
每一步都以前一步为前提。
Apply the third one to our table and you can see the fault.
把第三条用到我们那张表上，你就能看出毛病。
A booking decides which club it is for.
一条报名决定了它属于哪个社团。
And then the club decides the coach — not the booking.
而社团又决定了教练——不是报名决定的。
So coach reaches the booking through the club, in a chain, and that chain is a transitive dependency.
所以"教练"是通过"社团"才连到"报名"上的，形成一条链， 而这条链就是传递依赖。
It is exactly what third normal form forbids, and it is why the coach was repeated.
它正是第三范式所禁止的，也正是教练被重复的原因。
The fix is to break the chain: give the club its own table, with its coach.
解决办法是把这条链断开：给社团自己的一张表，连同它的教练。
The booking table then keeps only what belongs to a booking — the student — plus a club_id that points at the club.
报名表于是只保留属于一条报名的东西——学生—— 再加上一个指向社团的 club_id。
Now Mr Ng is written down once.
现在 Mr Ng 只被写下一次。
If he leaves, one row changes, and there is no second copy to disagree with.
如果他离职，一行改动，而没有第二份副本能跟它唱反调。
And nothing has been lost.
而什么都没有丢。
Whenever you want the coach beside the student, a join puts the two tables back together — and yes, the coach's name appears twice in that result.
任何时候你想让教练和学生并排出现，一次连接就把两张表拼回去—— 是的，在那个结果里教练的名字出现了两次。
That is the rule to end on: normalise for storing, and join for reading.
这就是要作为结尾的那条规则： 为"存储"而规范化，为"读取"而连接。
A repeated value is fine in an answer, and never fine in a table.
一个重复的值出现在答案里没问题，出现在表里永远有问题。
Four things to take with you.
带走四点。
One: repeating a fact in many rows is what normalisation removes.
第一：把一个事实在很多行里重复，正是规范化要消除的东西。
Two: first normal form is one value per cell, and second is no partial key dependency.
第二：第一范式是每格一个值，第二范式是不存在部分键依赖。
Three: third normal form means no column depends on another non-key column.
第三：第三范式意味着没有列依赖于另一个非键列。
Four: normalise for storing, and JOIN when you need it back.
第四：为存储而规范化，需要时再用 JOIN 拼回来。
Do the tasks — and that is the course.
做完题目——这门课就结束了。