relationship to the same table

  • Thread starter Thread starter J Holtendehouzer
  • Start date Start date
J

J Holtendehouzer

Hi! I'm designing a database that will check drugs for compatibility. This
requires that I compare one drug with other drugs in the same table
(tblDrugs).

The way I have it set up now is with another table (tblCompatibility) with
DrugID1, DrugID2, and a yes/no field for compatibility.

I think it will work fine, but it seems like a "clunky" solution to me. Is
there a "better" way?

Thanks!
Joe
 
Hi! I'm designing a database that will check drugs for compatibility. This
requires that I compare one drug with other drugs in the same table
(tblDrugs).

The way I have it set up now is with another table (tblCompatibility) with
DrugID1, DrugID2, and a yes/no field for compatibility.

I think it will work fine, but it seems like a "clunky" solution to me. Is
there a "better" way?

Thanks!
Joe
Click to expand...

This is called an "indirect many to many self join" and it is quite
appropriate, in my opinion. I'd suggest possibly adding some more
fields - drug compatibility really isn't a yes/no phenomenon; might it
not be better to think of this as a drug interactions table, and have
a field or fields indicating the nature of the interaction? For
example, some drugs can be taken by the same patient provided they're
taken some hours apart, or one drug might affect the metabolism or
bioavailability of the other but not be absolutely contraindicated.

If you do want to JUST flag incompatible drugs, you may not need a
yes/no field at all: name the table tblIncompatibility and only add a
record if the two drugs cannot be co-administered.

John W. Vinson[MVP]
 
Thanks for the help, John. Yes, actually I just didn't want to make the
asking of the question more complicated than the problem itself. I do have
a more elaborate exploration of the incompatibility.

You said "contraindicated." Is it Dr. Vinson?

Joe
 
Thanks for the help, John. Yes, actually I just didn't want to make the
asking of the question more complicated than the problem itself. I do have
a more elaborate exploration of the incompatibility.

You said "contraindicated." Is it Dr. Vinson?
Click to expand...

Ph.D. Chemistry, Berkeley 1972... and I've done essentially no
chemistry since <g>. (I did work in the pharmaceutical industry for
years though).

John W. Vinson[MVP]
 
Ahhh...
It's not a term found in the vernacular very often, so I figured. I went to
'SC, though, so I can't talk to you anymore! <g>

Joe
 
The way I have it set up now is with another table (tblCompatibility)
with DrugID1, DrugID2, and a yes/no field for compatibility.
Click to expand...

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
 
Tim Ferguson said:
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??
Click to expand...

Actually, the way I had to set it up in order to monitor interactions to my
own satisfaction is more complex. I had to use several different tables in
order to account for each type of interaction within each category of
interaction; for instance, a single drug may have several pharmacokinetic
effects on other drugs by altering absorption, distribution, metabolism, or
excretion. Furthermore, each drug may also have several types of
pharmacodynamic alterations, hemodynamic alterations, and a pharmaceutical
interaction potential (that one really is basically yes/no).

Another table exists to track allergic or idiosyncratic reactions a patient
may have had, which are independent of interactions, and let's not forget
about interclass reactions--I have a many-to-many relationship between drug
categories because most patients who are allergic to penicillins are also
allergic to cephalosporins (both are beta-lactam abx).

My patients are in good hands. I just needed some help with the coding of a
form.

Thanks!
Joe
 
Back
Top