3nf

[Access Developer]

I have the following table:-

tblPatients

PatientID PK
Forename
Surname
NextofKin
NextofKinDetails

I know that it's possible to have more than one patient
who can share the details of the SAME next of kin. Also
the next of kin can be a patient themselves. However if we
ignore these sceanrios for the moment, would the following
changes: -

tblPatients
========
PatientID PK
NOKID FK
Forename
Surname

tblNOK
=====
NOKID PK
Forename
Surname
TelNo


What I am interested in and the difficulty I am having is,
does the creation of tblNOK constitute 2nf OR 3nf? Or does
the the fact that if the 2 points that I asked u to ignore
above would mean 2nf or 3nf. I understand the definitions
of 2nf and 3nf but have difficulty in translating them in
the real world.

Any help would be appreciated.

 

[Lynn Trapp]

This seems, to me, to be one of those cases where you would probably need to
use a Self Join in your one table tblPatients. The logic behind this is that
every person you enter in the database as a Next of Kin is either also a
patient or a potential patient themselves. Thus you could have this
structure.

tblPatients
PatientID PK
Forename
Surname
NextofKinID FK (self joined to some other record in this same table)

In the NextofKinID you would store the PatientID of some other person in the
table. So, you might have 2 records like this:

PID Fname Sname NOKID
1001 Jack Smith 1114
1002 Jane Smith 1001

Doing this, the details for the next of kin are stored in the same table as
the patients -- since the next of kin is also a patient (or a potential
patient). Someone who is ONLY a next of kin might not have as many details
recorded as someone who is a patient.

 

[Tim Ferguson]

tblPatients

PatientID PK
Forename
Surname
NextofKin
NextofKinDetails

NextOfKinDetails is functionally dependent on NextOfKin, and transitively
dependent on the PK PatientID -- therefore this relation is in 2NF and not
in 3NF.

As an aside, if the "s" on the end of Details suggests that this field is
not atomic, it would not even be in 1NF; but this maybe shorthand for our
benefit.

The main problem with this design is not a normalisation issue, but one of
semantics. Most designers would recognise that Patients and NextOfKins are
both types of People and would work on that priniciple -- in fact you have
indicated that in many cases Patients are NextOfKins. Do remember that the
mathematics of DB design will only take you so far: at some stage you do
have to get down and understand the real-world behaviour of the things you
are trying to model.

All the best


Tim F