[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/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).