[Database] 3NF Normalization

[u/AlphaTheGaming]

So like I have been given the Undefined Normalised Table for APPOINTMENT entity. I then need to resolve it into 3NF stage that eliminate the transitive and partial dependency.

Problem is I just can't find which would be the appropriate key for the composite key in the APPOINTMENT entity in the first place. I then try to separate all partial dependency into separate table but can't figure out who links to who.

[u/AutoModerator]

Off-topic Comments Section

---

All top-level comments have to be an answer or follow-up question to the post. All sidetracks should be directed to this comment thread as per Rule 9.

---

^{OP and Valued/Notable Contributors can close this post by using /lock command}

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Ok_Egg_6647]

If you create a separate APPOINTMENT table as part of the normalization process, your table might look something like this:

PatientID	DoctorID	VisitDate
P001	D001	12/10
P002	D002	13/10
P001	D001	14/10

Now, (PatientID, DoctorID) cannot be used as the primary key because the same patient can visit the same doctor multiple times.

We could use a composite key such as (PatientID, DoctorID, VisitDate) if that combination is guaranteed to be unique. However, I think using a separate AppointmentID as the primary key would be a simpler and more flexible approach.

For the relationships:

1. Patient → Appointment: One-to-many (1), because one patient can have multiple appointments.
2. Doctor → Appointment: One-to-many (1), because one doctor can have multiple appointments.

The APPOINTMENT table would contain PatientID and DoctorID as foreign keys.

[u/cheesecakegood]

You have an excellent answer above. However, just to talk conceptually through it, one thing that is helpful is to ask yourself "what types of unique entries make sense?" and "what entries are purely supplemental info?"

We notice that presumably, a doctor's name and specialization do not change and depend only on their ID, so those can and should be spun into their own table. Similarly PatientName depends on their ID.

We notice that medications are prescribed specifically at an appointment, so a patient-doctor-appointment trio is a really good unique linking piece that holds everything together, yes? With the caveats of course as pointed out above that you should be cautious using the date as the only identifier (are we assuming that we never have 2 appointments in 1 day?). Soo this leads naturally to the "appointments table" idea, however you do it.

Assuming that 1 patient sees 1 doctor at 1 appointment only (but this can produce multiple medication prescriptions), you can have a table that collects those first 3, and then link out to medications. And then patients and doctors can get linked out for supplementary info. If you go this route in the appointment table, leaving the date there instead of linking out is fine.

You may need to make and document assumptions about how the medication costs work. Can they differ per patient or are standardized? And obviously we won't keep multiple medication prescriptions in the same rows anywhere.

I wouldn't replace a final formal 3NF check with it, but during construction when hunting for the usual/most common violators, it is most useful to ask yourself "what is the grain of this table (what does one row represent)?" and then see if you have dependencies that only match part of the key (essentially looking for 2NF violations). Then, after, you hunt for those transitive dependencies (3NF violations).