Medication data - complex grid

[foxtrot123]

I need to create a database that collects, among other things, medications (with dose information) for patients. Attached is a screenshot of the form currently being used to collect the medication data.

The client would like the database interface to reflect the form layout as much as possible. They are okay, though, with an interface that allows you to select only the medications that were endorsed on the form. (Continuous form style.) My challenge is how to handle the disparate dosage information, which will make a continuous form approach problematic. Any suggestions about how best to model this, other than creating many, many separate variables for every possible medication and dose?

Thank you.

 

[MarkK]

It doesn't make sense to me to replicate this form. This form is laid out this way because it is a paper system.

I would create an Add/Edit Medication wizard that knows how to handle different types of medications that might have different data collection requirements. Consider a single form with many tab pages and a next button and a back button. As a first step get the user to select the medication type, and then re-jig the wizard to collect data for that type of medication. If this is an edit, skip the medication selection step and populate the wizard with existing data, since for an edit the medication type will be known.

For display, I would only show medications that actually exist for a given patient, and not allow that data to be directly editable except via a wizard. A problem with your system seems to be that nothing is dated. Won't you need to be able to preserve information showing historical doses of medication? You must not be allowed to delete old prescriptions or doses, so how is dating going to figure in to your data creation?

 

[CJ_London]

I would create a separate form for each medication type and combine them as subforms onto a main medication list form

 

[foxtrot123]

I would create a separate form for each medication type and combine them as subforms onto a main medication list form

And as for the underlying table structure, four separate tables - one for each type of medication?

For example:

tblSwallowed
--------------
MedicationID
MedicationName
Dose
DoseOther

tblOral
--------------
MedicationID
MedicationName
Dose
DoseDuration
WhenPrescribed
WhatIsItFor

etc.

 

[CJ_London]

And as for the underlying table structure, four separate tables - one for each type of medication?

In principle yes, and would be the easiest option, but for the longer term it does depend on whether the structures are likely to change on a regular basis (addition of new medicines is not an issue, just create a new record). Also you have some inconsistencies (which may be perfectly valid) - for example, in swallowed you specify a variety of doseages plus other whilst in oral you do not have specified dosages.

So rather than using tick boxes you could have dosages as a combo box where limit to list is false, and the rowsource is populated with the dosages for the particular medicine (this is in line with Lagbolts point about not replicating the form)

So you might have a table structure as follows:

TblMedication
MedicationID autonumber PK
MedicationName Text (Swaloowed..., Oral..)

Medicines
MedicineID autonumber PK
MedicineName Text (Fluticasone, ...)
MedicationID Long FK

TblDetails
DetailID autonumber PK
MedicineID Long FK
DetailType Text (select from Dose, Formulation, Number of Puffs)
DetailRateText (eg. for Fluticasone Dose you would have 400 mcg daily, 880 mcg daily, etc, for Inhaled number of puffs you would have 4 records populated 1,2,3,4)

TblPatientMeds
PatientMedsID autonumber PK
PatientID long FK
MedicationID long FK
MedicineID long FK
DetailID long FK
Doseage double - select from list generated from TbleDetails which match to medicineID and detailID. In the case of tick boxes you would use -1 for true and 0 for false

So the first three tables are your 'lookups' to populate the last table - which would also need the other fields you mentioned (DoseDuration, WhenPrescribed WhatIsItFor, etc)

This will give you much more flexibility to allow for future changes

 

[ButtonMoon]

I need to create a database that collects, among other things, medications (with dose information) for patients. Attached is a screenshot of the form currently being used to collect the medication data.

Are you familiar with the healthcare metadata standards in use in your client's jurisdiction/organisation? In many places the use of metadata standards is a legal/regulatory requirement for healthcare applications. Even if they aren't mandatory in your case, the relevant industry standards would presumably help answer your questions for you. Don't attempt to design a data model from a form. Create the data model first based on data analysis and requirements gathering. Then build the UI to support it.