Modeling a prescription/medicine usage set of database tables

Not sure how to go about modelling medication taken at a point in time in a relational database. Each way I have thought of so far seems to have redundant data.

Need to be able to have a user say I took x tablets or y mg of some drug.

Currently I have a Diary table, which has a One to Many relationship with Entries. Each entry has description, date, time etc, which are all fine. But I want to be able to have the amount of medication taken.

Current thinking is to have a Drug table, which has a recursive many to many relationship with itself, so that one Drug can contain multiple other drugs, or itself may be the active ingredient.

So for the drug ‘codeine’ the active ingredient is ‘codeine’ (not actually the active ingredient, but illustrates the point)

But you also have drugs such as cocodamol, which contains codeine and paracetamol. So has to map to both the ‘codeine’ row and the ‘paracetamol’ row.

How would I be able to have a user with a diary, add an entry to say:

I took 2 cocodamol of 30mg codeine and 500mg paracetamol strength?

As a raw data structure it would be something like (ignoring all database parts):

{'diary': 
    {'entry': 
        {'date': '2014-11-11', 
         'medication': {
             'cocodamol': {
                 'codeine': 60, 
                 'paracetamol': 1000
             }
         }
     }
 }

The reason for this being a table and not just flat text field, is

a) I want to create statistics for medication taken
b) I want the drug table to stay generic so I can link information to that drug
c) Would be useful to store prescription data as well as diary entries, so a Dr has prescribed taking 2 cocodamol 30/500mg up to every four hours

Which begs the question where would weight go, and how is the data modeled in general.

Have drawn up a relation diagram of what i think it should be like, but i’m still unclear as to how i would have a dose of 30/500mg of cocodamol, the following should take care of any single dose, whatever the unit of measurement may be.

Would it be best to just have the input as {cocodamol: 30/500mg} then on the backend have two doses, one 30mg codeine and then one 500mg paracetamol?

This would probably mean linking Dosage to the ActiveIngredient table instead of Drug, and then using the reverse of that link to determine what the user took. So if we know they had a dose of 500mg paracetamol and 30mg codeine, which are both linked to the Drug cocodamol, we can recreate their medication taken instead of its constituent parts.

Cheers,

Brett

(there are fields missing from some of those tables, which only serve to cloud the issue (i think))

Answer

You’re very close.

Forget the weight, you’re veering unnecessarily from the natural data. You need an entity that describes the medication as your direct child of Entry. This includes a relation to the Brand Name (which you cannot possibly navigate to with your current model), and indicates both the drugs and the dose.

As an example, say you took a tablet of Percocet 2.5 mg/325 mg. (You can get Percocet in other doses)

There is a row in Brand Name:

id: 16, name:"Percocet"

There are two rows in Drug:

id: 5, name:"OxyContin"

id: 12, name:"Acetaminophen"

There are three rows in Medication:

id:20, name:"Percocet 2.5 mg/325 mg", brand_name_id: 16

id:21, name:"Percocet 5 mg/325 mg", brand_name_id: 16

id:22, name:"Percocet 7.5 mg/325 mg", brand_name_id: 16

…each of which has two child rows in Dose:

medication_id: 20, drug_id: 5, Unit:"mg", Quantity:"2.5"

medication_id: 20, drug_id: 12, Unit:"mg", Quantity:"325"

medication_id: 21, drug_id: 5, Unit:"mg", Quantity:"5"

medication_id: 21, drug_id: 12, Unit:"mg", Quantity:"325"

medication_id: 22, drug_id: 5, Unit:"mg", Quantity:"7.5"

medication_id: 22, drug_id: 12, Unit:"mg", Quantity:"325"

So now your Entry row is simply:

medication_id: 20 (plus date, time, notes, etc.)

I’d mess with the names a little, too.