If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
3nf
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. |
#2
|
|||
|
|||
3nf
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. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Access Developer" wrote in message ... 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. |
#3
|
|||
|
|||
3nf
"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 |
Thread Tools | |
Display Modes | |
|
|