"Access Developer" wrote in
:

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