Normalisation

[aziz rasul]

I have the following denormalised table.

Table - “tblConsultants_and_Patients”
Consultant Tel Number Department Patient 1 Patient 2 Patient 3
Naomi Smith 01736 762831 Rheumatology Catherine Peters Sandra Hutchinson
Beverly Jones 01841 215874 Neurology Nathaniel Wood Sarah F. Collins Spencer Watson
Muhammad Omar 01736 742326 Orthopaedic Amina bint Hamid Tom Quentin Catherine Peters
James Hodges 01637 365874 Cardiology Alice Abernathy Grace Havers
Ann Hutchinson 01736 763298 Vascular Tom Quentin Jacque DeBlanc
Mary Wheeler 01872 632587 Neurology Wilfred Knott Lolita D. Guardino
…


I have changed the table into 2 normalised tables. However I have patients that are being treated by more than one consultant. Am I doing this right?

Table - “tblConsultants”
Consultant ID Consultant Tel Number Department
1 Naomi Smith 01736 762831 Rheumatology
2 Beverly Jones 01841 215874 Neurology
3 Muhammad Omar 01736 742326 Orthopaedic
4 James Hodges 01637 365874 Cardiology
5 Ann Hutchinson 01736 763298 Vascular
6 Mary Wheeler 01872 632587 Neurology
…

Table - “tblPatients”
Patient ID Consultant ID Patient Name
1 1 Catherine Peters
2 1 Sandra Hutchinson
3 2 Nathaniel Wood
4 2 Sarah F. Collins
5 2 Spencer Watson
6 3 Amina bint Hamid
7 3 Tom Quentin
8 3 Catherine Peters
9 4 Alice Abernathy
10 4 Grace Havers
11 5 Tom Quentin
12 5 Jacque DeBlanc
13 6 Wilfred Knott
14 6 Lolita D. Guardino
…

 

[KKilfoil]

If a patient can have many consultants, and a consultant can have many patients, then you have a many-to-many relationship.

Seatch this forum for many great examples of how to do this.

 

[Pat Hartman]

To be more clear. You need a third table. tblpatients, tblConsultants, tblConsultations. The Consultations table contains the PatientID and the ConsultantID and creates the relationship between the other two tables.

 

[aziz rasul]

I see what you mean Pat. Many thanks.

 

[Mile-O]

And, if possible, split all the names to forename and surname.

 

[aziz rasul]

Good one. I forgot that. Thanks for the reminder.

 

[aziz rasul]

I have the following table:-

tblPatients

PatientID
ConsultantID
Forename
Surname
DateofBirth
NextofKin

I'm trying to get this table to 3nf. Am I right in saying that the NextofKin needs to go and placed in a separate table with a link on PatientID? I'm terrible with normalisation, so forgive me if this is a simple question.

 

[The Stoat]

Hi Aziz,

Interesting.

If 2 patients have the same next of kin i.e two daughters of the same Father then strictly speaking you should use a link table, as you have between patient and consultant. In that way if the next of kin details i.e. phone number, changes then you would only need to alter them once and all of the patients will be upto date. It will also allow you to have an infinite - within reason- number of next of kin for any given patient

I would be interested to know what you are working on. I have a fair amount of experience with NHS datasets

The Stoat

 

[aziz rasul]

Actually I don't work for the NHS, but I have a similar example to the one I posted. Since I always have difficulty with normalisation, I thought I would come up with an example that I could refer back to in the future wherever I work.

Is the solution u provided conform to 3nf or 2nf? It's a 3nf example that I am looking for and understand.

 

[The Stoat]

Hi,

Def of Second Normal Form (SNF) An entity is said to be in SNF if it is in first normal form and if every non-key attribute is functionally dependant on the unique identifier.

Basically this means that the fields within the table relate only to the primary key of that table.

As an example. If we know the name of the country we can determine the name of its capital city. Therefore the capital is "functionally dependant" on the country

In your example if we held the patient and the next of kin and the next of kin's telephone number in the same table then this would not be in SNF

The phone number of the next of kin does not belong to the patient.

So we split the table. We have one table for patients and another for next of kin with their phone number in it.

So your tables are in SNF.

Def of Third Normal Form (TNF): An entity is said to be in TNF if it is in SNF and if all non-key attributes are mutally independant.

Basically this means that fields should not be held in tables that contain information that can be calculated from another field.

For example this table is in SNF, but the field Age(Days) can be calculated from the field Birth Date and the current date. By removing this "transative dependancy" it will be in third normal form.

Therefore your tables will be in TNF because they have non of this type of field.

Tbl_Patient

Patient Number-------Birth Date------Age(days) <--- we can calculate this
------1-------------06/06/2004----------2----


As Pat posted you should not have many to many relationships between two tables. In your case. 1 patient may have many next of kin. 1 next of kin may belong to many patients. In order to correctly link these two tables you need a third table. i.e TBL_PatientKin. In this table you should have the primary key from the Patient table and the primary key from the Next of Kin table.

This basically prevents you from repeating records and allows you to make changes once to the next of kin record that will reflect in all of the patient records i.e when the next of kin changes their telephone number.


I hope that isn't to confusing

The Stoat

 

[KKilfoil]

As Pat posted you should not have many to many relationships between two tables. In your case. 1 patient may have many next of kin. 1 next of kin may belong to many patients. In order to correctly link these two tables you need a third table. i.e TBL_PatientKin. In this table you should have the primary key from the Patient table and the primary key from the Next of Kin table.

This basically prevents you from repeating records and allows you to make changes once to the next of kin record that will reflect in all of the patient records i.e when the next of kin changes their telephone number.


I hope that isn't to confusing

The Stoat

You could take this one step further. The Next of Kin (NoK) COULD be a patient as well. Rather than repeating data in two tables, the many-to-many linking table could be used to 'self-reference' the patient table (which I would then consider remaining to 'People') to identify the NoK. So, no matter what, there will only exist one record in one table to describe a single person.

For those people who fall into the category of 'Next of Kin, NOT a patient', you could add a yes/no field (say 'NonPatient'), to flag those records that need only contain sufficient data for the NoK purposes.

HTH

 

[aziz rasul]

Wow. I'll need to think about the last 2 posts and get it around my head. I'm off tomorrow so I'll repost on Thursday.

 

[aziz rasul]

Thanks for the last 2 posts. Just 1 last thing to complete my understanding of 3nf. In the patients table, what kind of field (if added) would not conform to 3nf other than a calculated field.

 

[The Stoat]

Thanks for the last 2 posts. Just 1 last thing to complete my understanding of 3nf. In the patients table, what kind of field (if added) would not conform to 3nf other than a calculated field.

Take an example of a course enrolment table. We need to know who has enrolled on which course what dept they are from and the start date of the course. We have two tables.


Tbl_Staff.

StaffID   Staff-FName   Staff-SName   Dept
100    John              Smith            Accounts

Tbl_Courses

CourseID   Course-Title
IND1         Staff Induction

We can create a third table called Tbl_Courses_Enrolled_On to record the information we want.

This could be designed as follows and would be in SNF

COURSE NUMBER   START DATE   ATTENDEE NAME   ATTENDEE DEPT
IND1                   27/03/1998     JOHN SMITH         ACCOUNTS

However If we know the staff members name we know their dept. We can say that the department is functionally dependant on the staff member name. However the staff member name is not the unique identifer for the member of staff- there could be two John Smiths so we use a StaffID -

In fact the table should look like this

COURSE NUMBER   START DATE    StaffIDFK  
IND1                   27/03/1998     100

We can then link the staff table to the Tbl_Courses_Enrolled_On using the StaffID as a foreign key called StaffIDFK.


We have now removed any functionally dependancies and the table is in TNF.

HTH

TS

 

[aziz rasul]

I completely understand what you are saying Stoat. A good example to understand. I now also understand why I am finding difficulty with TNF. I find it difficult to extinguish (sometimes) between SNF and TNF in the real world. I understand the definitions but find it difficult to translate them in tables. I do understand that calculated fields should be calculated within queries.

From what you have said in the last post, transferring the NextofKin into a separate table with appropriate links of course, I am doing TNF not SNF?? Right?

 

[The Stoat]

Hi,

We'll keep this simple. KKilfoil has a point you could keep both next of kin and patient in the same table and then link the 2 records through a link table where one reocord is the next of kin to a patient record.

Personnaly my approach would be to have a seperate next of kin table. The reason being with a large number of records in a Patient Table >200,000 in most hospitals if you want to query on this it wastes valuable time having to remove all the records that are next of kin first. Trust me, some queries can take 20-30 minutes to run you don't want any more parameters in your queries than are strictly necessary. Anyway.

Patient table containing only details relating to the patient.

PatientID(PK); Patient NHS number;Title (i.e MR); Fname; Sname; address (1 column for number, 1 for house name, 1 for street,1 for town, 1 for county, 1 for country if applicable) postcode;work telephone number;home tel number; mobile number ; email address; DOB.

Next of kin table .

As above you can change the field names like PatienID to NextofKinID.
You can then use a query to transfer the data from next of kin to the patient table if they become a patient.

Link Table PatientID; NextOfKinID.


This is in TNF. Nothing in either of the two main tables, Patient or next of kin is functionally dependant on anything other than the PrimaryKey of that table.

To work this out. If i gave you the name of the patient can you tell me the name of the town they live in? No. There may be more than 1 patient with that name.
If i gave you the name of the town can you tell me which county is it in? Possibly, but there may be two towns with the same name so the answer is still No. You can do this with any of the fields and the answer should always be No unless you say. If the PK is 100 can i find out about the patient. Then the answer is yes.

Remember that the PK can be made up of more than 1 field (a compound key) but i believe it is worth using a unique index autonumber-

( NOT , I repeat NOT , as part of the key. You only use an autonumber as a key where there is no "natural" key. A natural key is like the following. To be employed everyone should have a national insurance number so that is a natural key for a staff table.

The useful part of using an unique index autonumber is that the number will be unique. If we have a table with a compound key of say three fields it makes it much easier and smaller to store the autonumer field in a link table.)


-It also helps to speed up searches and helps when you want to display subforms on mainforms. To this extent i use them on the link tables with the same proviso, they are not part of the key they are simply an index, some may disagree.

The link table contains only the keys. And is therefore in TNF.

Hope that helps

TS

 

[aziz rasul]

OK Stoat, I think I understand now. I will probably struggle to establish 3nf when designing new tables\databases. However when I look back at this thread I should be able to work it out. Many thanks for your input and patience.