We are automating tracking of prescriptions
(medicines prescribed) for OHIP holders.
We want to develop a database for this that tracks
patients
(any OHIP holder who has been prescribed medicine),
physicians
(physicians who prescribe medicine under OHIP),
conditions
(medical conditions patients have for which medicines are prescribed),
prescriptions
(the actual precriptions that physicians write for patients),
medicines
(that are prescribed),
and
pharmacies
(where the patients get their precriptions filled).
Each patient will have a unique OHIP number.
We want to know the patient's name, birthdate, address,
and the expiry date on his or her OHIP card.
Each OHIP-certified physician
(the only physicians we care about here)
has a unique physician number assigned to him or her.
We want to know the physician's name,
address (of his or her main office),
and medical specialty.
A physician sees a patient
if the patient has ever had an appointment with that physician.
We want to track which physicians see which patients.
A physician may diagnose a patient to have a condition.
A physician may only write a prescription for a patient
whom he or she sees,
and which treats a condition that the patient has
been diagnosed to have.
(Note that the physician who writes the prescription does not need
to be the same physician who diagnosed the condition.)
We should track the conditions that a given medicine treats.
Note that certain medicines may be used to treat different conditions,
and that a given condition may be treated by different medicines.
The situtation with medicines is a bit more complicated.
A physician prescribes a brand,
not a medicine itself.
A given medicine may be made
by one or more pharmaceutical companies.
We call a medicine as made by a given pharmaceutical company
a brand.
Thus, we need to track both brands and medicines in the database,
and the pharmaceutical companies that make the brands.
For each medicine,
we want to know its name (which is unique, in this case).
For each pharmaceutical company,
we want to know its name (unique) and the address of its headquarters.
For each brand,
we want to know its name,
the pharmaceutical company that makes it,
what medicine it is,
the dosage amount
(how much of the medicine is in each pill or designated amount),
and its price (per dosage).
The brand's name, the name of the company that makes it,
and the dosage together suffice to identify
a given "brand" as designated in a prescription.
For each precription,
we want to know, of course,
the patient it is for,
the doctor who prescribed it,
the brand it is for
(which tells us the medicine and dosage),
the quantity prescribed (number of dosages),
and the date the prescription was written.
We also want to know the pharmacy at which
the prescription was filled and when (the date),
if it was filled.
We want to track OHIP-certified pharmacies
(the only pharmacies we care about here).
Each such pharmacy has a unique pharmacy number assigned to it.
In addition,
we want to track the pharmacy's name and address.
We very much would like the database design to enforce
the following for us, if it is possible.
We want that any prescription
must be for medicine that treats a condition
that the patient has been diagnosed to have.
In other words,
we do not want to honour prescriptions for medicines
that do not match any of a patient's conditions.
