Identify the PK in given tables

Question

Tables are:

Patient(PatientID, FirstName, LastName)
Disease(DiseaseID, DiseaseName)
PatientDisease(PatientID, DateID, DiseaseID)

It's clear that Patient and Disease tables have PatientID and DiseaseID as PKs, but what about PatientDisease table knowing that each patient in a single day can be diagnosed with a single disease?

Possible duplicate of http://stackoverflow.com/questions/893874/mysql-determine-tables-primary-key-dynamically — Siyual, Jun 21 '14 at 17:42
DateID - what is it? How is the Date table defined? Maybe it is only named Date, but actually it stores date and time that makes PatientDecease table quite logical(it is hardly possible that someone can be diagnosed with two deceases in one second). — Eugene Podskal, Jun 21 '14 at 17:43
Your qeustion is not clear. What do you mean, what about it? What does a table knowing mean? And are you *telling us* that each patient can only be diagnosed with a single disease per day? Do you want to know the PK of PatientDisease? (If so, you haven't given enough information.) Please edit to be clear. — philipxy, Jun 22 '14 at 22:28

score 0 · Accepted Answer · answered Jun 21 '14 at 17:44

0

I think, that it here you should use composite PK with all 3 fields: PatientId, DateId, DiseaseId. No matter one disease per day can be diagnosed or not, all 3 fields are mandatory for patient disease.

answered Jun 21 '14 at 17:44

Solorad

904
9
21

score 0 · Answer 2 · answered Jun 21 '14 at 17:46

0

Eigher you can go with composite key

or

add another column patient desiese-id to work as unique row number

answered Jun 21 '14 at 17:46

Ajay2707

5,690
6
40
58

Eugene Podskal · Answer 3 · 2014-06-25T13:35:15.160

I think that a patient that can be diagnosed with only one decease in day is very lucky one, but this system won't be so lucky - it has hefty design flaw for any real application. Doctor who is unable to write that I've both caught a cold and had the allergy on antibiotics could bring a potential disaster.

And key like (PatientID, DateID, DiseaseID) doesn't allow you to add multiple deceases. It is maximal physically possible key but it is still not enough for this table to be even in the first normal form for your problem domain.

Even if the Date table actually stores both date and time that won't make PatientDecease table very logical(it is hardly possible that someone can be diagnosed with two deceases in one second, but it doesn't solve the problem completely - consider some bulk decease updates).

1) So you should either add some surrogate key

PatientDisease(PatientDiseaseID, PatientID, DateID, DiseaseID)

and your key will be PatientDiseaseID

2) or add an ordinal number of the decease for patient in the day:

PatientDisease(PatientID, DateID, DiseaseID, PatientDeceaseInDayOrdinal)

with primary key (PatientID, DateID, DiseaseID, PatientDeceaseInDayOrdinal)

The first idea is probably better and much simpler.

Identify the PK in given tables

3 Answers3