Hi, folks. I'm currently working for a residential school. We have a large database system that tracks, students, staff, parents, etc. Now the nursing department wants to get in on the fun, as the majority of our kids take medication.

In thinking about this, I'm wondering about the relationship: One student can have many medications (Janie takes Pill A, Pill B and Pill C). Also, one medication can be given to several students (Pill B is given to Janie, Johnny and Suzie). Sounds like the dreaded many to many, which I've been taught to avoid. How would you divvy this up?

We already have a students.fp5 DB, and my initial inclination was to make a Medication DB with our StudentCode as the key field. Now I'm not so sure.

Dosages also comes into play to further complicate things. Janie can get 20mg of Pill A at 8:00 AM, 15mg of Pill A at 3:00 PM and 20mg of Pill A, 10mg of Pill B, 0.5mg of Pill C plus a multivitamin at 8:00 PM (or any other crazy combination you can think of). Agghh! So then I thought maybe there should be a "dosages" DB, in which "8:00 AM Pill A, 10mg, Janie" was one record, and so on.

I don't usually have a problem with this type of thing, but this one has me stuck. How would you do it?

Short answer: Yes, you need more files (or upgrade to 7 and use multiple tables).

Students file:

Student1

Student2

[These next two could be merged]

MedicationSchedule file:

Records:

Student1 takes pill A at 8:00 am

Student1 takes pill A again at 12:00 pm

Student1 takes pill B at 3:00 pm

Student1 takes pill A at 8:00 am

StudentMedications file:

Student1 takes pill A

Student1 takes pill B

Student2 takes pill A

Medications file:

Pill A

Pill B

The MedicationsSchedule file and the StudentMedications could be 1 file, if no Student ever takes the same pill twice a day. Or you could bend the rules slightly and use a repeating field(s) for the schedule (of one particular Student-Medication), since you're not tracking the actual taking of the medication (like a hospital would). But then you couldn't sort it, so it could get pretty ugly if you did much editing of the schedule.

"Sounds like the dreaded many to many, which I've been taught to avoid."

I don't know who taught you that but don't listen to them. Many to many relationships are common real world things you need to model, and are not really that tough. You just need to understand the concept of a join file, as described by Fenton.

You may already know this, but if you're tracking medicatin, make sure your database meets any policies and local laws wrt security, especially with schedule II and III controlled substances (logging access, tracking transactions, etc). And of course there's HIPAA to worry about.