如何规范化数据库中的数据？

On a very basic level, any repeating values in a table are candidates for normalization. Duplicated data is usually a bad idea. Say you needed to update a patient's surname - you now have to update all the occurrences in this table, and possibly many others throughout the rest of the database. Much better to store each patient's details in one place only.

This is where normalization comes in. Looking down the columns, you can see that there are repeating values for data about dentists, patients and surgeries, so we should normalize towards having tables for each of these entities, as well as the original table that contains appointments, giving you four tables in total.

将实体提取到它们自己的表中，并为每一行提供一个主（唯一）键-现在仅使用一个递增整数。（编辑：根据评论中的建议，我们可以使用PatientNo，StaffNo和SurgeryNo的自然键来代替代理。）

然后，我们不会在约会表中多次出现每个患者的姓名和号码，而只是在Patient表中引用主记录的键。这称为外键。

然后，对“牙医和外科手术”执行相同的操作。

您最终将看到如下所示的表：

APPOINTMENT

AppointmentID  DentistID  PatientID  AppointmentTime   SurgeryID
----------------------------------------------------------------
1              1          1          12 Aug 03 10:00   1
2              1          2          ...               2
3              2          3          ...               1
4              2          3          ...               1
5              3          2          ...               2
6              3          4          ...               3


DENTIST

DentistID  Name             StaffNo
--------------------------------------
1          Tony Smith       S1011
2          Helen Pearson    S1024
3          Robin Plevin     S1032


PATIENT

PatientID  Name              PatientNo
---------------------------------------
1          Gillian White     P100
2          Jill Bell         P105
3          Ian MackKay       P108
4          John Walker       P110


SURGERY

SurgeryID  SurgeryNo
-------------------------
1          S10
2          S15
3          S13