Patients profiles database

Question

I am making software for patients in hospitals. I want to make an option that if a doctor has prescribed a drug that interacts with another drug that the patient already uses, a warning will appear explaining that the two drugs shouldn't be administered together.

There will be data in the database for every drug, with its composition and its interactions with other drugs.

How can I link between drug interactions and make that feature possible in my design?

For example:

• patient: Albert Ricky
• diagnosis: asthmatic patient
• treatment: salbutamol inhalation

When salbutamol is written in the patient profile in the database, a warning message should appear:

WARNING: that patient has been prescribed atenolol since 10-9-2010 as a hypertensive drug and that interacts with salbutamol, causing a severe rash and swollen edema.

Answer 1

You could have one table that tracks what a patient takes, and another that tracks which drugs interact with which:

patient_drugs
-------------
   patient_id (FK to main patient table)
   drug_id (FK to main drug table)


drug_interactions
-----------------
   drug_id (FK to main drug table)
   reacting_drug_id (FK to main drug table)
   interaction_description (text)

Then to find a patient's drug interaction you could maybe do this:

select *
from drug_interactions 
where drug_interactions.drug_id in (select drug_id
                                    from patient_drugs
                                    where patient_id = $PATIENT_ID)