Question Patient management DB normalization help

[SaviorSix]

Hello

I am currently designing a patient/practice management database, this will be to retrospectively track the demographics and patient information for those who visit a particular department.

There are several divisions (roughly 3) in this department that the patient can visit.
Each division has different information that is required to track, Therefore, I have seperate tables for each area. (not sure if this is the correct approach but it seems the correct solution)
The fields were developed by each division and sent to me. The 'DoS' (date the patient came in) was common to all areas, so I put it in its own table (Encounters)

Patients
-PatientID (auto pk)
-fields for patient demographics

PatientPregnancies
-PregnancyID (auto pk)
-PatientID (fk)
-fields for each patient pregnancy

Encounters -date the patient came in - DoS and PatientID are a unique index
-EncounterID (auto pk)
-DoS (date of service)
-PatientID (fk)

Now this is where the problem begins - three tables for the divisions.

GIVisits
-GIVisitID (auto pk)
-EncounterID (fk)
-Data for GI visit

InpatientVisits
-InpatientID (auto pk)
-EncounterID (fk)
-Data for Inpatient visits

OBMedVisits
-ObMedID (auto pk)
-EncounterID (fk)
-Data for OBMed visits

And for each distinct visit in each area, there have to be diagnoses and billing information
Each visit can have one BillingCard, and each BillingCard has many Diagnoses.

VisitBillingCard
-VisitBillingCardID
-BillingCode
-Details

VisitDiagnoses
-VisitDiagnosesID
-VisitBillingCardID (fk)
-DiagnosisCode


I have no idea how to uniquely link each visit table to the one VisitBillngCard table.

Is my approach all wrong to begin with?

 

[speakers_86]

It is hard for me to help too much, because I don't understand the medical lingo. However, I can give some pointers. I think you need a junction table. In the table Encounters, add a number field that signifies whether they are their for pregnancy, GI, Outpatient, or what have you. You should be able to link the billing table to the encounters table (one bill per visit).

Additionally, I have a few other questions/comments. Why is there a gender field in the patient pregnancy table? If the patient is pregnant, I have a pretty good idea of the gender. That field should be in the patient table.

In regards to the tabacco and drug fields, instead of having one for current, and one for past, why not just have one? The combo box options can be Never, Past Use, Current Use. Seems to make more sense to me than Yes/No.

 

[SaviorSix]

It is hard for me to help too much, because I don't understand the medical lingo. However, I can give some pointers. I think you need a junction table. In the table Encounters, add a number field that signifies whether they are their for pregnancy, GI, Outpatient, or what have you. You should be able to link the billing table to the encounters table (one bill per visit).

Additionally, I have a few other questions/comments. Why is there a gender field in the patient pregnancy table? If the patient is pregnant, I have a pretty good idea of the gender. That field should be in the patient table.

In regards to the tabacco and drug fields, instead of having one for current, and one for past, why not just have one? The combo box options can be Never, Past Use, Current Use. Seems to make more sense to me than Yes/No.

Thanks for the response - the gender fields is the gender of the baby.

The tobacco/drug fields, along with all the other fields, have been pre-determined by the docs to be the fields they want to collect, along with the responses they want, so I have to incorporate them as-is.


Billing being linked to the encounter table - I'm not sure this will work, because the billing information is dependent on the area where the patient was seen (GI, ObMed, Inpatient) So If the patient is seen in GI, that particular GI visit has its own billing information, If the patient is seen at ObMed, that ObMed has its own billing information. I am thinking that the billing table needs to be linked to each area table....?

If the billing table is linked to the encounter table, how will the billing data be linked to the visit data?

Maybe I should get rid of the Encounter table altogether, and just link the ObMed/Inpatient/GI tables directly to the Patients table, using PatientID as the FK?

 

[speakers_86]

I'll take a closer look tonight.

 

[speakers_86]

Maybe I should get rid of the Encounter table altogether, and just link the ObMed/Inpatient/GI tables directly to the Patients table, using PatientID as the FK?

I didn't really take another look yet, because I was out late, but your thought seems straight forward enough. I would probably try that. If you need a list of encounters, then I am sure you can accomplish that with queries.

 

[SaviorSix]

I didn't really take another look yet, because I was out late, but your thought seems straight forward enough. I would probably try that. If you need a list of encounters, then I am sure you can accomplish that with queries.

Right, but now when I link each visit area table to the billing table, the information gets duplicated, because the PK of each seperate visit area table can repeat as the FK in the billing table.....


I've been messing around, and have tried putting all the visit area data into ONE table, as data in the table rather than columns in seperate tables, and then populating the visit table via a query that inserts the data specific to the field visited...

 

[SaviorSix]

Here is the re-design.

The append query inserts the data fields from "VisitAreaData", depending on the "AreaID" selected.

So if Inpatient is selected, all the VisitData for the Inpatient visit is inserted into the VisitDetails table (parent table is the Visit table, which contains the PatientID and the Date)

Then all the entry person has to do is enter the responses into the Response field of the VisitDetails table for each corresponding data field