The way I have it set up now is with another table (tblCompatibility)
with DrugID1, DrugID2, and a yes/no field for compatibility.
Drug interactions are a mite more complex and more simple than this.
There are recognised interactions, each of which relates to more than one
drug vis:
AntiEpilepticDrugs induce Liver Enzymes; so you need a list of all the
AEDs and and all Hepatic-Metabolised Drugs and then the instruction
"Monitor and increase dose of HMD". You therefore need a few more tables:
Drugs(*PharmaCode, EnglishName, VariousDetails, etc)
Interactions(*InteractionID, BriefDescription, FullDetails,
PrescriberNotes)
DrugOne(*InteractionID, *PharmaCode, InteractionType, SpecialNotes)
The final table looks a bit like
Phenytoin, AEDLiver, Inducer, "Archaic anyway"
NaBarbiturate, AEDLiver, Inducer, "Potent +++"
Warfarin, AEDLiver, Hepatic, "Beware of bleeding after dicontinuation"
This way it's easy to get all drugs involved in a particular interaction,
all interactions relating to a particular drug, all interactions that
feature two particular drugs, etc. etc. It's actually probably safer to
make sure all drug entries are related to their pharmacological groups,
and link the interactions to the groups rather than the individual drugs.
Just missing out one line could have horrendous complications.
On that note, the thought of doing this as a learning project makes my
ears bleed -- please reassure me that you won't be monitoring real
patients with this thing??
B Wishes
Tim F