I had a lecture at college recently about normalizing databases, but since we are online I can't really ask a lot of questions, so here I am.
Can someone tell me if I'm doing the conversion right, if not where am I mistaking?
# Patient Nos
Patient Name
Ward Nos
Ward Name
Bed Name
# Drug Number
(Drug Name)
(Description)
(Dosage)
(Method)
(Units Given)
(Start Date)
(Finish Date)
# Patient Nos
Patient Name
Ward Nos
Ward Name
Bed Name
# Patient Nos
# Drug Number
Drug Name
Description
Dosage
Method
Units Given
Start Date
Finish Date
# Patient Nos
Patient Name
Ward Nos
Ward Name
Bed Name
# Patient Nos
# Drug Number
Drug Name
Description
# Patient Nos
Dosage
Method
Units Given
Start Date
Finish Date
# Patient Nos
Patient Name
Ward Nos
Ward Name
Bed Name
# Patient Nos
# Drug Number
Drug Name
Description
# Patient Nos
# Drug Number
Dosage
Method
Units Given
# Patient Nos
Start Date
Finish Date
Let's start with the Patient. The only information we have about a patient is his number and name.
Patient
_______
Patient Nos
Patient Name
Next, we have a Ward. Based on the report, a ward can have multiple patients, while a patient is in one ward. We model a one to many relationship with a foreign key.
Patient
_______
Patient Nos
Patient Name
Ward Nos (FK)
Ward
----
Ward Nos
Ward Name
Finally, we have a bed. A ward has multiple beds, while a patient has one bed. Since all we know about a bed is the bed number, we'll add that to the patient table as another foreign key to a table we don't have any information to define.
Patient
_______
Patient Nos
Patient Name
Ward Nos (FK)
Bed Nos (FK)
What do we know about a drug?
Drug
----
Drug Nos
Drug Name
Drug Description
What else do we know? A dosage is a relationship between a drug and a patient. So let's make our first attempt. I'm assuming that the start and end date has to do with the drug, since it's on the same line as the drug.
Dosage
------
Dosage Nos
Dosage Type
Dosage Method
Dosage Units Given
Dosage Start Date
Dosage End Date
Drug Nos (FK)
Patient Nos (FK)
Now, we've tied the dosage to the drug and the patient. However, we haven't tied the patient to the dosages. A patient can have multiple dosages, and dosages can belong to more that one patient. When we have a many to many relationship, we use a junction table to tie them together.
PatientDosage
-------------
PatientDosage Nos
PatientDosage Units Given
PatientDosage Start Date
PatientDosage End Date
Patient Nos (FK)
Dosage Nos (FK)
We moved the units given, start date, and end date to the PatientDosage junction table, since these fields have to do with both the patient and the dosage. So the final Dosage table would look like this.
Dosage
------
Dosage Nos
Dosage Type
Dosage Method
Drug Nos (FK)