Help with normalization

ALui

Registered User.
Local time
Today, 02:30
Joined
May 11, 2009
Messages
19
Hi, so I've recently found out I should've normalized my tables before starting to enter data... but now I've got a big table but it doesn't make sense to me how to group things together. Attached is an image of what I have currently.

What doesn't make sense to me is that each table needs some kind of primary key, but the only ones that make sense to use are the healthcare number and the number of the referring doctor!
 

Attachments

  • Original.jpg
    Original.jpg
    32.1 KB · Views: 174
Healthcare_Province should be a foreign key to a Prvovince table.

Appointments should be in their own table with a foerign key to patient. This allows multiple appointments to be recorded without repeating the patient information.

You would also include the consultiing doctors key in this table. They may not always see the same doctor. They may also be referred by different doctors on different appointments so I would put this in the appointment record.
 
Just so we're on the same page
healthcare_province is the province that pays for the public health care
province is the province where the patient lives.

healthcare_province is required for out of province patients, for example patients who come from BC to Alberta for surgery. so there is no guarantee that someone lives in AB and has a healthcare_province of BC.

can you please check out the picture link? so far it's what i think is right, but there seems to be a problem where i can't have the same referring doctor more than once for any patient OR have 2 appointments on the same day.
 

Attachments

  • normalize.jpg
    normalize.jpg
    43.3 KB · Views: 178
Heading in the right direction.

Your main table will be Appointment as this will be constantly added to. The other tables just tell information detail about who is attending. Patients will have multiple appointments so you should not have the appointment key in the Patient table but vice versa.

Take appointment date field out of Patient_List and add a field for the Patient_List key to Appointments. This is to indicate which patient is attending the appointment. The relationship between Patient and Appointment is more like the Doctor and Appointment. One patient and one doctor are associated with the appointment.

You won't be able to use Appointment Date as a key in Appointments (unless you only have one appointment each day). A PK in the Appointment table might not be essential. It would only be required if you needed to refer to the appointment from other records.

I would also have a table of locations with info about that location like LocationID, address, keyholder, telephone, etc. The Location in Appointment will be the LocationID key from this table.

Addresses may require another field to cover two line addresses. Address1 and Address2 is quite common in databases.

In Appointment I would also add checkbox field to indicate if the patient turned up. This would show which appointments are outstanding. You might also include a field for Rescheduled where they don't attend. This is where you would use a PK in Appointment. Rescheduled would hold the PK of the new appointment.

Patient email and Doctor email might be useful. The referring doctor would also have an address. The Appointment would also have an attending doctor unless this database is just for one doctor. But what if the practice expands?
 
For a patient couldn't you just reschedule them by putting them down for another day?
moz-screenshot.jpg

Thanks very much for all your help, if this practice expands to more than one doctor, I think it would be a job for someone other than a first year student in civil engineering ;). Attached is (what i THINK) is the end point, or very close to.
 

Attachments

  • normalize2.jpg
    normalize2.jpg
    46.9 KB · Views: 147
Normalization is crucial to taming stubborn databases. Search this forum's database design topic for various articles on entity analysis. Otherwise you are going to have serious trouble reining in what can be a really tough topic.

As this is a variant of a "bookings" scheme, look up "Bookings" and "Reservations" in this forum. The topic has occurred many times and might offer insights.
 

Users who are viewing this thread

Back
Top Bottom