A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

3nf



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2004, 10:30 AM
Access Developer
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 01:57 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default 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  
Old June 11th, 2004, 11:07 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.