table structure to document patient medications

[foxtrot123]

I'm building a database that needs to document details about patients' medications (name, dose, etc.). The client is interested only in 10 specific medications that he's identified ahead of time.

Initially I planned to create a many-to-many between tblPatients and tblMeds, and use a continuous form to indicate the patient's medication(s) and details. But the problem is that the details needed for each medication often vary, so the idea of a continuous form with fixed headings and controls quickly breaks down.

For example, below are four of the medications and the kinds of details they need:

1. Med A: Dose (drop down with 4 fixed options, the last option being "Other"). If other, then specify: ______
2. Med B: Dose (numeric)
3. Med C: Dose (numeric) plus a question about whether it's prescribed for Asthma or Bronchitis
4. Med D: Dose (numeric) plus a question about whether it's in mgs or mcgs, plus a question about it's formulation (pill, liquid, or other)
5. etc.

Is there a decent way to handle this in a way that promotes normalization but produces a useful GUI? The best I've achieved is a heavily normalized questionnaire database approach (tblQuestions, tblResponses, etc.), but this often yields a vary tall continuous form that feels a bit unnatural when it comes to data entry:

Rspns QstnTxt
[dropdown] "Medication A (yes or no)"
[dropdown] "... If yes, select dose"
[dropdown] "... If other dose, specify"
[dropdown] "Medication B (yes or no)"
[dropdown] "... If yes, enter dose"
etc.

Since I'm dealing with a short (and fixed) list of medications, should I avoid the idea of a many-to-many and continuous form, and just hard code every variable into a table (like below)?.

MedA_YN (Y/N; checkbox)
MedA_Dose (text; dropdown with the four text options)
MedA_DoseOther (text)
------------------------
MedB_YN (Y/N; checkbox)
MedB_Dose (numeric)
------------------------
MedC_YN (Y/N; checkbox)
MedC_Dose (numeric)
MedC_Purpose (text; dropdown with "asthma" or "bronchitis")
------------------------
MedD_YN (Y/N; checkbox)
MedD_Dose (numeric)
MedD_DoseMetric (dropdown with "mgs" or "mcgs")
MedD_DoseForm (dropdown with "pill", "liquid", or "other")

Thanks for any suggestions.

 

[John Big Booty]

You may get some inspiration from on of the data models here.