Duplicate Relationships in Rails/MySQL Healthcare Application

Question

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.

Answer 1

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:

therapies
 - id
 - drug_id
 - milligrams

And Prescription becomes:

prescription
 - id
 - therapy_id
 - condition_id

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:

SELECT DISTINCT prescriptions.condition_id, therapies.drug_id
FROM prescriptions
  INNER JOIN therapies ON prescriptions.therapy_id = therapies.id

You could use a query like the above to create a view called usages that could be used by a read only Usage model in Rails. If performance became a concern you could use that to create a cached usages table and setup a database trigger to update the cache table when a new prescription is added though.