panache Posted November 22, 2004 Posted November 22, 2004 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?
Fenton Posted November 22, 2004 Posted November 22, 2004 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.
bruceR Posted November 23, 2004 Posted November 23, 2004 "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.
Barbecue Posted November 24, 2004 Posted November 24, 2004 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.
Recommended Posts
This topic is 7362 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now