It doesn't seem like that design is quite right. If the patient's reference number uniquely identifies the patient, then that should be the primary key. If the same patient can register for a given disease multiple times, then that should be part of the key in the junction table, not in the patient table.
The primary key in the junction table should be what uniquely identifies an association, which in this case should be a composite key composed of the key from the patient table, the disease table, and the registration date.
PatientDisease table
-------------
PK, FK PatientID
PK, FK DiseaseID
PK Date
关系将是
Patient --1:n--> PatientDisease <--n:1-- Disease
I would suggest you one more table for cases. This table would contain an entry date. You should not use the disease name as primary key. This would make it difficult to correct typos or simply to choose a more appropriate name in the future, or maybe you would want to have a Latin as well as an English name.
Patient table
-------------
PK PatientID
Name
DateOfBirth
etc.
Disease table
-------------
PK DiseaseID
Name
Case table
-------------
PK CaseID
FK PatientID
EntryDate
etc.
CaseDisease table
-------------
PK, FK CaseID
PK, FK DiseaseID
Now you have these relations
Patient --1:n--> Case --1:n--> CaseDisease <--n:1-- Disease
EDIT:
The case table might not be necessary for now and might seem to be over-designed. However, should it turn out in future, that you have to store other data to a case as well; the db design would not have to be changed fundamentally.
UPDATE:
Alternatively, you could do it without a case table. In that case, the junction table would have a date as part of the primary key
PatientDisease table
-------------
PK, FK PatientID
PK, FK DiseaseID
PK Date
'name' as a primary key is not a good idea - there shoud be an Id column in that table which should be set as primary key
DiseasesToPatients table should be made of both foreign keys - from Patients and Diseases tables and they should be set as composite primary key on that table.
发布评论
评论（3）
看起来这个设计不太对劲。如果患者的参考号唯一标识患者，那么该应该是主键。如果同一患者可以多次注册某种疾病，那么它应该是连接表中键的一部分，而不是患者表中的键。
连接表中的主键应该是唯一标识关联的主键，在本例中应该是由患者表、疾病表和注册日期中的键组成的复合键。
It doesn't seem like that design is quite right. If the patient's reference number uniquely identifies the patient, then that should be the primary key. If the same patient can register for a given disease multiple times, then that should be part of the key in the junction table, not in the patient table.
The primary key in the junction table should be what uniquely identifies an association, which in this case should be a composite key composed of the key from the patient table, the disease table, and the registration date.
我建议您再多一张表来存放案件。该表将包含输入日期。您不应使用疾病名称作为主键。这将使纠正拼写错误或将来选择一个更合适的名称变得困难，或者您可能想要一个拉丁语和英语名称。
Patient table ------------- PK PatientID Name DateOfBirth etc.Disease table ------------- PK DiseaseID NameCase table ------------- PK CaseID FK PatientID EntryDate etc.现在您有了这些关系
编辑：
案例表现在可能不是必需的，并且可能看起来过度设计。但是，如果将来您还必须将其他数据存储到案例中；数据库设计不必从根本上改变。
更新：
或者，您可以在没有案例表的情况下完成此操作。在这种情况下，连接表将有一个日期作为主键的一部分
关系将是
I would suggest you one more table for cases. This table would contain an entry date. You should not use the disease name as primary key. This would make it difficult to correct typos or simply to choose a more appropriate name in the future, or maybe you would want to have a Latin as well as an English name.
Patient table ------------- PK PatientID Name DateOfBirth etc.Disease table ------------- PK DiseaseID NameCase table ------------- PK CaseID FK PatientID EntryDate etc.Now you have these relations
EDIT:
The case table might not be necessary for now and might seem to be over-designed. However, should it turn out in future, that you have to store other data to a case as well; the db design would not have to be changed fundamentally.
UPDATE:
Alternatively, you could do it without a case table. In that case, the junction table would have a date as part of the primary key
The relations would be