One-to-many relationship table design

[Guest]

Hi, I hope someone can help advise me. I have a database with a one-to-many
relationship between two tables. In the one table I have the fields
Medication# and then MedicationName. In the many table i have PatientID#,
Medication# (these 2 have keys on them), and then I have the dosage, time and
notes fields.
My problem is that I have a patient who takes a certain medication, e.g ,
medication# 6 but he takes this in the morning and again in the evening, and
the dosage is different for each time. So i want to create a new record for
the morning details and the evening details, but when I put the evening
details in, it won’t allow me to because the patientID#, and medication# are
the same (duplicates) as the previous record for the morning. But the dosage
and time entries are different. So I’d like to be able to enter records for
patients who may possibly have the same medication each time (and date) but
with different dosage and time details. Can anyone advise me what I would
need to do (in simple terms!)?

 

[Guest]

Take the dosage and time out of the PatientMedication table and put it in a
new PatientDosageTime table. Don't restrict yourself to morning and evening.
You should have:
Patient table - has list of patients
Medication table - has list of medications
PatientMedication table - cross-references patients to medications (many-many)
PatientDosageTime table - how much medication and when

You could also put the dosage in the medication table, so you would have:
PainKiller 10mg
PainKiller 25mg etc.
as separate medications
Then you would not need it in the PatientDosageTime table
It depends what works for you best.

-Dorian

 

[Guest]

Hi, Many thanks for responding to my message. i've created a new table for
the PatientDosageTime and put in PatientID#, dosage, time. I don't
understand, however, how to 'cross-reference patients to medication'. In the
relationships window, which fields would I match to which and in which table?
I've decided to keep the dosage and time in the new table. Hope you can help
- I'm just a beginner!

 

[Guest]

Hi, Many thanks for responding to my message. i've created a new table for
the PatientDosageTime and put in PatientID#, dosage, time. I don't
understand, however, how to 'cross-reference patients to medication'. In the
relationships window, which fields would I match to which and in which table?
I've decided to keep the dosage and time in the new table. Hope you can help
- I'm just a beginner!

 

[Guest]

Hi, Many thanks for responding to my message. I’ve created the new table and
put in PatientID#, dosage and time. The PatientMedication table now just has
PatientID# and Medication# and the MedicationList table has the Medication#
and MedicationName. I don’t however, understand how to “cross-reference
patients to medications†(many-many). Can you explain this or tell me how I
could do this in the relationships window – which fields in which table would
I drag to each other to create the many-to-many relationship you mentioned?
Thanks for helping.

 

[Guest]

Your Patient table should have a unique primary key say PatientID (autonumber)
Your Medication table should have a unique primary key say MedID (autonumber)
Your PatientMed table consists of 2 columns:
PatientID - which links back to Patient table
MedID - which links back to Med table
make both columns a combined primary ID so you cannot get duplicates.

A patient can have many meds and a med can be used by many patients but you
only want each patient to have a given med once.

-Dorian

 

[Guest]

Hi Dorian, thanks again for helping. I’ve fixed the tables up as you said,
but I wanted the patient to be able to have more than one of the same meds,
for example if he took painkillers, 2 tablets in the morning and 1 in the
evening – he would have then 2 records of taking painkillers – the same med
but with different dosage and time. Is this overcome with the
PatientDosageTime table you suggested creating – which fields should I have
in this and how do they link to the other table(s)?

 

[Guest]

It might actually be better (simpler) to do away with the PatientMed xref
table and have both links in the MedDosage table, so you would have:
PatientID - links to Patients
MedID - links to Medications
Dosage
Day/Time

Also, I think I would lean toward putting the dosage in the medication table
so you would have like
Painkiller 10mg
Painkiller 20mg as separate medications.
But I am thinking about medications with a finite number of dosages, you
might be dealing with things like liquids that can be prescribed in any
amount in which case that might not be prattical.

Sorry, I am thinking aloud in designing this...

-Dorian

 

[Guest]

Hi Dorian,

Many thanks for your help - I think I've fixed it up now using your
suggestions, but i kept the dosages in with the meddosages table, as you are
right about the different types - mg, ml, 20, 30....etc

Best regards.