 |
Home > Section 7 > Subsection 7 (Normalisation - part 1) |    |
Learning outcomes for this page:
1. Be aware of the incorrect method of attempting
to solve problems with repeating groups
2. Be aware that fields with large numbers of null
values suggest a poor database design
Look at the following three records from a 'patients visits' table. A research doctor designed the table to collect data concerning patients visits to her or two other colleagues:
Doctor visits table:
|
Doc id |
Doc name |
Patient name |
|
1 |
smith |
Gray Harvey Anderson Smith |
|
2 |
Jarvis |
Hewitt |
|
3 |
Goodall |
Farmer |
The 'patient name' field is Multi-valued. That is the field contains more than one value in one or more records. One way that people often try and get around this is by dividing these values into additional fields in the same table thus (this is not the correct solution!):
|
Doc id |
Doc name |
Patient name (appointment 1) |
Patient name (appointment 2) |
…… |
Patient name (appointment 4) |
|
1 |
smith |
Gray |
Harvey |
… |
Smith |
The problem with the above is that you will need to create a whole set of empty fields for each record to cater for the maximum number of visits you will allow a patient to see a doctor. Most of these will be empty and you may run out of possible fields. It is always best to avoid fields with null values (the reasons for this are complex see Date 1995).
Key point: Avoid fields containing null values
Note: for our purposes you can equate null to mean an empty field.
For: Clinicians | NHS managers | Non healthcare workers
Written by Robin Beaumont & Chris Noden e-mail:robin@ieg-net.co.uk or chrisnoden@hotmail.com