jurovure jurovure - 10 months ago 69
SQL Question

clinical questionaire relational db

I have a form which (among other things) asks whether a patient has certain medical conditions and whether any of his/her parents have/have had certain medical conditions. What is the best way to store this info in a relational DB?

For the sake of discussions let's consider a single medical condition, diabetes. Please note that I have at least 25 conditions to record.

I have considered two possible solutions, but I'm not satisfied with either.

1. Use an Examination table with the following fields:

PatientId -- integer
Diabetes -- boolean
DiabetesFather -- boolean
DiabetesMother -- boolean

The problem here is that the number of fields exploded; for each condition I have 3 fields.

2. Use a Conditions table with the following fields

PatientOrPatientParentId -- integer
Diabetes -- boolean

In this case the parents of the patients have to be introduced in the DB as pseudo-patients.

jpw jpw
Answer Source

There are many possible solutions, and an alternative to the ones listed in the question would be model it using three tables, like this:

Patients (PatientId (PK), PatientName, ...)
-- example: 1, "Patient1"
Conditions (ConditionID (PK), ConditionName, ...)
-- example: 1, "Diabetes"
PatientCond (PatientID (FK), ConditionID (FK), PatientStatus, FatherStatus, MotherStatus)
-- example: 1, 1, true, false, true

Using a junction table is a common solution for modeling many-to-many relations.