RE: One-to-many relationship table design

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"Liverlass3981" wrote:

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)?

"mscertified" wrote:

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

"Liverlass3981" wrote:

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!

"mscertified" wrote:

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

"Liverlass3981" wrote:

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!)?

.

Relevant Pages

  • RE: One-to-many relationship table design
    ... PatientID - links to Patients ... I think I would lean toward putting the dosage in the medication table ... Your Medication table should have a unique primary key say MedID ... A patient can have many meds and a med can be used by many patients but you ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Logging On for a Second (or Third) Opinion
    ... I knew that medical research on the internet had become both easier ... Internet users look for health information online, ... for patients seeking further information after a diagnosis. ...
    (alt.support.arthritis)
  • Re: GHB/ Xyrem-- anybody know?
    ... Patients will not be required to pick up their medication from the VA; ... 3.2% of patients receiving sodium oxybate reported ... During clinical trials, 2 suicides and 1 attempted suicide were reported ...
    (alt.support.chronic-pain)
  • Re: GHB/ Xyrem-- anybody know?
    ... Patients will not be required to pick up their medication from the VA; ... 3.2% of patients receiving sodium oxybate reported ... During clinical trials, 2 suicides and 1 attempted suicide were reported ...
    (alt.support.chronic-pain)
  • Support Programs Can Increase Treatment Adherence And Persistence, Reduce Relapses For People With M
    ... patients who receive injectable MS medications through a community ... a leading pharmacy benefits ... that patients receiving specialty drugs, like those used to treat MS, ... them manage their health and medication therapy, ...
    (alt.support.mult-sclerosis)