Overview of the Application
I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and also provide more general information about the utility of drugs to treat various conditions.
Current Entities
“Drug” – Example: “Abilify”
“Dosage” – Example: “10mg”
“Condition" – Example: “Depression”
Potential Entities For Required Use Cases
“Prescription” (Drug + Dosage + Condition) => This is required for dosage specific data, for a specific condition. Clinical research may be attached to this entity.
“Therapy" (Drug + Dosage) => This is required because it represents how most medications are priced, and how most medications are interchanged between 3rd party health applications, using a government standards primary key known as a RxNorm Concept Unique Identifier.
“Usage” (Drug + Condition) => This is required for connecting drugs to conditions, without consideration to dosage. Some drugs are used to treat many conditions.
Concern
These three models, if implemented, create duplicate relationships between either drug and condition, or drug and dosage. How can we build out a normalized system that supports our needs for both dosage and condition specific tools, as well as dosage and condition agnostic tools?
Example
“Abilify 5 mg for depression“ => The “Prescription” entity would allow us to attach clinical studies, and alternatives, for 5 mg of Abilify in the treatment of depression. The “Therapy” entity would allow us to find pricing, as well as 3rd party supplemental data, for 5 mg of Abilify. The “Usage” entity would allow us to track all conditions that Abilify is used to treat, along with their efficacy in treating these various conditions.
Best Answer
This question if part database schema and part rails. From a Rails perspective, you can create as many aliased relationships as you like so normalization isn't as much of a concern there.
Without seeing the schema and just going based off of what you have written above though, I'd say that
Dosage
doesn't belong in a table with it's own model.Dosage is effectively
Amount: 10 Unit Of Measure: mg
Writing things like 10mg into a table and giving them an id doesn't make a ton of sense. Organizing the types of units of measure that you have available or even standardizing by converting everything to milligrams in the database so that you can just do conversions when displaying would probably make the most sense.
Then you eliminate the
dosages
table completely and simplify to:And Prescription becomes:
Now, usage is the outlier here because it's where your only real "duplicate" relationship is but everything you need to link drugs to conditions can be inferred from your prescription data, effectively like so:
You could use a query like the above to create a
view
calledusages
that could be used by a read onlyUsage
model in Rails. If performance became a concern you could use that to create a cached usages table and setup a databasetrigger
to update the cache table when a newprescription
is added though.