best way to handle yes/no fields

[SadGrl]

Trying to help someone else with designing a database for medical conditions. We're at the table design stage and we're trying to figure out the best way to handle yes/no data. I've totally confused myself by this point over what is probably a very simple problem.

For instance, we have a table 'Abdomen' which lists different descriptions of abdomen conditions (for example, soft, tender, etc). A client can have multiple abdomen conditions. In the original medical form that the table is based on, the list of abdomen conditions appear with a yes/no answer beside each condition. So what would be the best way of designing a table, knowing that the form we design and report we print will have a yes/no answer for each of these conditions.

We started off with a table that had an AbdomenID (PK) field and a AbdomenCondition field as headers, with each condition name (soft, etc) listed under AbdomenCondition. This seemed correct to me, but how would I deal with the yes/no part? I couldn't make a separate column for yes/no answers could I? (because it would change per client)

Alternatively, I thought we could have the condition names as fields, with yes/no as data input but then the records (AbdomenID) wouldn't really be unique since you have instances where data is repeated within a record (for example all yeses)

But then I thought maybe combining the above with another table that contained a relevant unique PK (for example medical record--MedicalRecordID, which could contain all other medical data and just link to a client). But that would make one big table.

Suggestions?

 

[The_Doc_Man]

Here's the question: Can you ever have two abdominal conditions at once?

If so (say, abdoment bloated and tender, for example), then your only real choice is that these symptoms would have to be in a child table which is sparsely populated. Looking ahead, you need to consider that there are other things besides abdomens. So for example, left-knee might be sore, swollen, visibly bruised, etc....

Having a yes/no field for every possible symptom would become prohibitive. There, you would need either an abdomen record that violated the "repeating group" normalization rule (having multiple yes/no fields in a record) or you would need to have a child record for every possible symptom - and for which lots of "NO" answers would be stored. If you use sparse storage philosophy, you only store records for which the answer is YES. After which it is easy to list them.

I would see a patient table tied to a child "visit" table tied to a child "list of symptoms" table. I.e. child, parent, grandparent tables.

Patient: PatID (PK, autonumber), name, DOB, etc.

Doctors: DrID (PK, autonumber), name, etc.

Visit: VisitID (PK, autonumber), PatID (foreign key - FK), DrID (if patient could see more than one doctor), etc.

Symptoms: SymptomID (PK, whatever source works best), description

VisitSymptoms: VisitID (FK), SymptomID (FK), BodyPartID (FK to a body part table, not described here)

VisitDiagnose: VisitID(FK), DiagnosisID (FK to a table not described here)

You have to look for potential many-to-one cases. One patient, many visits. One visit, possibly more than one symptom, possibly more than one diagnosis. The whole thing is going to be an issue the moment someone comes down with runny nose and coughing and fever at the same time. Gee, how often do you think THAT happens?

If you have lots of lookup tables - for patient, doctor, symptom, location of symptom, diagnosis, etc., you can drive reports every which way.

For instance, join doctor to visits to backtrack a doctor's schedule for a given day. Join patient to visits to see how often the patient comes to the office. Join visit to symptoms for a given date to see what a patient complained about. Join visit to diagnosis to see what the doctor thought.

I listed several prime keys as autonumber, but in fact any existing scheme to preserve uniqueness works just as well. For instance, there is such a thing as a doctor's medical association number that might be a candidate for DrID (PK). There is such a thing as a diagnosis code (used by insurance companies) that would probably be a candidate for diagnosis PK. There is a symptom table used by insurance companies too. Lots of choices here. Room for some really elegant JOIN queries.

If you have distinct visit IDs, you can probably also make a start for a billing history. You can tie lab results back to a visit where those tests were ordered.

This is a nice, big, juicy design with lots of room for cleverness and normalization. Give yourself some time to think about all the possibilities. They aren't endless, but this is one of those problems where you have lots of options on how to design what is needed.

 

[SadGrl]

Thanks! That was really helpful--actually answered a lot of questions I hadn't even asked yet.

You were right--patients can have multiple symptoms simultaneously.

One clarification, since I'm trying to visualize the tables in my head. By 'sparsely populated' do you mean I should stick with my option 1 table (ionly AbdomenID and AbdomenSymptom fields) and then just using that table as a lookup table. When imputing into a form, I would then select (or check off from a list) the symptoms present, rather than putting yes or no?

 

[The_Doc_Man]

"Sparsely populated" means that you don't store anything that isn't so.

Let's take the opposite case, densely populated. You would have child entries for that visit for Abdomen tender, Abdomen bloated, Abdomen displays boborygmus, etc. But maybe only one of those would be checked. You would have to print out a long check list and find the check marks. If you had 50 possible descriptions of abdomens, you would have 50 child records each time someone made a visit.

Making it sparse means you don't store Yes/No at all. What you store is any symptom code for which the answer would have been to check the corresponding YES box. If you have one symptom out of 50, you store one record, not 50 records. You only keep the symptoms that apply. This is sparse storage philosophy.

 

[SadGrl]

Thanks for clarifying, you've been a great help