Capturing Contact Details for Patient Database

[koolwaters]

Hi! I am creating a database to capture patient details and I have a query regarding the best method to capture the demographics.

Initially, I had created a table called tblPatient, where all of the patient details is captured and a table called tblEmerContact, where all of the details for a particular patient's emergency contact is captured. I then created a junction table called tblPatientEmerContact to link a patient with their emergency contact as a patient can list more than one person as their emergency contact.

An issue has now occurred where the emergency contact for one patient can also themselves be a patient and in the current set up, it means that a patient who is also an emergency contact for someone else will be duplicated in the database- their details would be noted in tblPatient as well as tblEmerContact.

Any suggestions on the best way to handle this issue?

 

[Ethvia]

Hi Kool

I'm not sure this is the answer you want, but here's how I would do it.

I would only use 2 tables, one of them would contain the patient details including a unique identifier for each patient (like an MRN). Then I would create the emergency contact table that includes whatever fields you want (name, address, phone number) and include a field to store the unique identifier from your patient table (the emergency contact table would have a different number as it's primary key). This would allow you to link as many emergency contacts as you need to one patient.

The only downside I can think of to doing it this way is that if one emergency contact was the same for multiple patients you would need to have the emergency contact in the emergency contact table multiple times. I don't really see that as an issue though because you would/could easily update the emergency contact info each time the patient came in.

 

[koolwaters]

Hi! Thanks for your reply but this will present the same problem where any changes that need to be made would have to be made several places in the database.

 

[james_lankford]

but is this really a problem ?

surely in a hospital doctors and nurses can themselves become patients

and I don't see a problem with having their names occurring in two different tables

yes, if the doctor or nurse change their phone number you'll have to update it in two different places
but I'd be ok with that

 

[Micron]

Anyone designated as an emergency contact should show up in the patient table which is the other way around from what was suggested. Think of this as a parent/child record relationship. The patient is the parent, the contact is the child. Therefore the contact table should contain the primary key from the patient table to avoid having the same contact in more than once for multiple patients (as was noted could be required doing it the other way). If a contact ever became a patient, it would have no effect. Should the contact for a patient change, only one record needs to be updated.

 

[james_lankford]

Anyone designated as an emergency contact should show up in the patient table which is the other way around from what was suggested. Think of this as a parent/child record relationship. The patient is the parent, the contact is the child. Therefore the contact table should contain the primary key from the patient table to avoid having the same contact in more than once for multiple patients (as was noted could be required doing it the other way). If a contact ever became a patient, it would have no effect. Should the contact for a patient change, only one record needs to be updated.

I don't understand what you're saying

my understanding of the original question was this

Patient table 
PatientID  PatientName    
  1           Abe Lincoln

Contact table
ContactID  ContactName
  1           Goorge Washington

Linking table 
PatientID ContactID
  1               1

and then one week later George Washington becomes a patient

Patient table 
PatientID  PatientName    
   1          Abe Lincoln
   2          Goorge Washington

Contact table
ContactID  ContactName
  1           Goorge Washington
  2           Mrs Washington 

Linking table 
PatientID ContactID
  1               1 
  2               2

and the problem was that Goorge Washington exists in two places
and because it's misspelled it has to be changed in two places

and the asker would like the name to exist in only one place so correction updates would only have to be done in one place

at least I thought that's what the problem was

 

[Micron]

patient table

ID	PatName
1	George
2	Sam

<tbody>
</tbody>

contact table

ID	PatientID	ContName
11	1	Mike
12	2	Dave

<tbody>
</tbody>

The contact for George is Mike. The contact for Sam is Dave. There is no third table because it is not necessary. You present the relationships between the two as a query or recodset. If Dave becomes a patient, you add him to the patient table and add his contact to the other table. If Sam has more than one contact, patient ID 2 shows up in contact table for each contact recorded. If contact phone number changes, you only make one edit in that table. The idea of a linking table violates the rules of normalization.

 

[koolwaters]

Thank for the response.

James_Lankford, you understand correctly. My problem is that having having the demographics in both the patient and emergency contact table can be an issue. For example, both my parents and I have the same general practitioner. So if I change my address and telephone number, more than one change has to be made. I may go to see the doctor and the receptionist change my address details in the patient table but she may not know that I am in the database as an emergency contact as well resulting in inconsistent data.

 

[koolwaters]

Hi Micron,

The setup you suggested violates the rules of normalization. In the Emergency Contact table, you need to know the person's address, telephone numbers and email address. This information is also required for patients. I can see too much duplication of effort as is.

 

[Micron]

I think have a clearer picture now - maybe not a solution, but here goes. The only way to have a single address record for a person regardless of what they are is to have all of that info in the same table and distinguish them from other people some other way.
One table needs a composite index using something like FName, Lname, birth date because two people in the same household will have the same name address and phone number (Frank Smith and Frank Smith junior) but not the same birth date. Just using Lname and birth date may not work if Mr. and Mrs. happened to be born on the same date. From what I've read elsewhere, SSN numbers are not reliable either, but you may know of a truly unique id that you can use because there is no guarantee junior has a middle name either. This table also needs 2 checkbox fields - Patient; Contact. A patient form returns only the patient records (checked True) but possibly identifies if the patient is also a contact. You cannot use one field for this and set it to one or the other because a person can be both. It's not like an employee table where the person is a supervisor OR a laborer. The same form can be used to serve up the contacts info (where Contact = True) as before. The checkbox field records will have no values in one field or the other for many of the records, unless a person is a contact and a patient. Not perfect, but I would live with that because the alternative is worse and it is common practice. You would not break out phone numbers into two tables just because a business has a phone number but not a fax number. As for forms, you might prefer a main form with the patient info and a subform datasheet listing all the contacts and info.

I'm not sure what is meant by a "linking table" posted above, but an actual table to "link" the two presents the same normalization issue. I hope I understand it now and that the solution is a viable one.