Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2006
    Posts
    52

    Help with Relational Model for Prescription

    Hi, I am creating a small program for keeping track of my family health records, I created 3 tables, Doctors, Patients, and Bill (or appointment?)
    Bill table has the following fields:
    BillID
    DoctorID
    PatientID
    Illness
    Prescription
    LabTests
    Date
    Notes


    Now, I want to have a separate table to keep track of prescriptions (it will help me with auto completing medicine names), but I really do not know how to do that?
    I thought about creating a table named Prescription with

    PrescriptionID as Primary key (& same field will be foreign key in Bill table)
    Medicine , but then how to I include many medicines in one field? I want to be able to auto-complete medicine names in this field, and if many names are written in one field it won't work, In addition, I want to write dose of each medicine, and when I fill the main form (Bill form), how would I go to the Prescription form?
    I searched in google trying to find an example, but didn't find what I want.

    Please any help would be highly appreciated, I am running crazy with my children getting sick too frequently and I want to keep track of their visits and medicine, I used to register everything in my phone's calender, but it is not efficient when searching. I would be grateful for any help.

  2. #2
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by oakeyes View Post
    Hi, I am creating a small program for keeping track of my family health records, I created 3 tables, Doctors, Patients, and Bill (or appointment?)
    Bill table has the following fields:
    BillID
    DoctorID
    PatientID
    Illness
    Prescription
    LabTests
    Date
    Notes


    Now, I want to have a separate table to keep track of prescriptions (it will help me with auto completing medicine names), but I really do not know how to do that?
    I thought about creating a table named Prescription with

    PrescriptionID as Primary key (& same field will be foreign key in Bill table)
    Medicine , but then how to I include many medicines in one field? I want to be able to auto-complete medicine names in this field, and if many names are written in one field it won't work, In addition, I want to write dose of each medicine, and when I fill the main form (Bill form), how would I go to the Prescription form?
    I searched in google trying to find an example, but didn't find what I want.

    Please any help would be highly appreciated, I am running crazy with my children getting sick too frequently and I want to keep track of their visits and medicine, I used to register everything in my phone's calender, but it is not efficient when searching. I would be grateful for any help.
    Whenever I hear including many of anything in one field, I start to cringe. Instead of doing that, make another table called Medication. Within that table, your attributes (also called fields) could be something like this: Medication ID (Primary Key), MedName, Description, Milligrams, Dosage PatientID (Foreign Key).

    I'm not sure about auto-filling, but I'm sure other members on this forum can assist. Worst comes to worst, try to look it up. I know that at least one site out there has to have something like that. But, one thing you could do is have a drop-down box on the form, so you wouldn't have to auto-fill anything, you could just select the medication.

    The Bill table would work the same way: BillID, Date, Amount, DoctorID(Foreign Key), PatientID (Foreign Key). Now, you know which patient saw which doctor, and how much the bill was.

    One piece of advice I give everyone is don't try to do everything at once, you'll end up with a huge headache. Rather, figure out what tables you need, then define your attributes (also called fields), then define your relationships. Say it out loud so you can see if it makes sense. Also, remember to name your tables singular and not plural. It will make it much easier when defining relationships.

    I hope this helps! Post back if you have anymore questions.

    Martin

  3. #3
    Join Date
    Aug 2006
    Posts
    52
    Martin, thanks a lot for the explanation
    Actually I already created the database, I have been working on it a couple of weeks, and I had three tables(Doctor,Patient,Bill), and yesterda I entered many records, but the defect is that I created the prescription (or medication ) as a memo field, someone here already recommended me to make a sperate table and now I am seriously considering it. The problem is that I do not understand how it works, you say:

    Medication ID (Primary Key), MedName, Description, Milligrams, Dosage PatientID (Foreign Key).
    AND I have in table Bill the following attributes(BillID, PatientID(foreign key), DoctorID(foreign key, Illness, Prescription(or Medication), Date, Notes)

    does this mean that Medication will be linked to table Bill thru PatientID? or linked to tablePatient thru PatientID, um sorry silly question but really totally lost

    What I do not understand, how would I know the whole list of medication of today's visit if I give a different medication value to each medicine ??
    For example, for today's visit to the doctor (suppose BillID = 1) and ( PatientID =1), the doctor gave me 3 drugs, so, should this be
    Medication ID :1
    MedName: Adol
    Dosage: 2 Capsules twice a day
    PatientID: 1

    MedicationID: 2
    MedName: MultiVitamin
    Dosage: 1 pill at Night
    PatientID: 1

    MedicationID:3
    Medname: Augmentin
    Dosage: 5 ml
    PatientID: 1

    If I use unique Medication ID for each record I will be able to enter only one reference in the table Bill (I mean in the attribute MedicationID as a foreign key, I will enter for example medication ID =1) and won't be able to know the whole list of medicine for each visit
    OR
    I can make one medication ID for the whole list ie.
    Medication ID :1
    MedName: Adol
    Dosage: 2 Capsules twice a day
    PatientID: 1

    MedicationID: 1
    MedName: MultiVitamin
    Dosage: 1 pill at Night
    PatientID: 1

    MedicationID:1
    Medname: Augmentin
    Dosage: 5 ml
    PatientID: 1

    In this case, I will be able to say that in Bill1 I should go to MedicationID 1 and then know the whole list of drugs, but in this case It won't be a primary key coz values of Medication ID in this case will be duplicating! which shouldn't happen in primary keys

    This puzzles me, I know stupid question but I need to understand that, I searched in my old College ERD course book but couldn't find a close example
    If you have an example even if it is not related to medication, any other business type, I would be grateful, I want to put a snaptshot of the relationalship of my current table so that you know fields I have but um working in a different computer currently...Thanks a lot in advance I appreciate it
    Last edited by oakeyes; 11-14-11 at 04:31.

  4. #4
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by oakeyes View Post
    Martin, thanks a lot for the explanation
    Actually I already created the database, I have been working on it a couple of weeks, and I had three tables(Doctor,Patient,Bill), and yesterda I entered many records, but the defect is that I created the prescription (or medication ) as a memo field, someone here already recommended me to make a sperate table and now I am seriously considering it. The problem is that I do not understand how it works, you say:

    Medication ID (Primary Key), MedName, Description, Milligrams, Dosage PatientID (Foreign Key).
    AND I have in table Bill the following attributes(BillID, PatientID(foreign key), DoctorID(foreign key, Illness, Prescription(or Medication), Date, Notes)

    does this mean that Medication will be linked to table Bill thru PatientID? or linked to tablePatient thru PatientID, um sorry silly question but really totally lost

    What I do not understand, how would I know the whole list of medication of today's visit if I give a different medication value to each medicine ??
    For example, for today's visit to the doctor (suppose BillID = 1) and ( PatientID =1), the doctor gave me 3 drugs, so, should this be
    Medication ID :1
    MedName: Adol
    Dosage: 2 Capsules twice a day
    PatientID: 1

    MedicationID: 2
    MedName: MultiVitamin
    Dosage: 1 pill at Night
    PatientID: 1

    MedicationID:3
    Medname: Augmentin
    Dosage: 5 ml
    PatientID: 1

    If I use unique Medication ID for each record I will be able to enter only one reference in the table Bill (I mean in the attribute MedicationID as a foreign key, I will enter for example medication ID =1) and won't be able to know the whole list of medicine for each visit
    OR
    I can make one medication ID for the whole list ie.
    Medication ID :1
    MedName: Adol
    Dosage: 2 Capsules twice a day
    PatientID: 1

    MedicationID: 1
    MedName: MultiVitamin
    Dosage: 1 pill at Night
    PatientID: 1

    MedicationID:1
    Medname: Augmentin
    Dosage: 5 ml
    PatientID: 1

    In this case, I will be able to say that in Bill1 I should go to MedicationID 1 and then know the whole list of drugs, but in this case It won't be a primary key coz values of Medication ID in this case will be duplicating! which shouldn't happen in primary keys

    This puzzles me, I know stupid question but I need to understand that, I searched in my old College ERD course book but couldn't find a close example
    If you have an example even if it is not related to medication, any other business type, I would be grateful, I want to put a snaptshot of the relationalship of my current table so that you know fields I have but um working in a different computer currently...Thanks a lot in advance I appreciate it
    I believe I do understand what you're asking, and trust me, it takes a while to wrap your head around what a relational database actually does. Let me give you an example of how this works relative to your situation.

    Martin goes to see Dr. Bob. Dr. Bob tells me I need to start taking a multivitamin, and allergy medication, and a steroid for my hives. I then walk over to the receptionist and pay her my co-pay of $10.00. Then I go to the drug store and fill my prescriptions, and come home. The dosage for the medications are as follows:

    Multivitamin -- 1x day
    Allergy pill -- 2x day
    Steroid -- 3x day

    Now, based upon that whole case, we can pick out exactly what we need, which you have already done. The question still remains: How do I keep track of the information I just received. More specifically, what do I do if my doctor gives me more than one medication? The answer is a one-to-many relationship. Let me break it down for you:

    I see one doctor who prescribes me many medications.

    I would have a Doctor table, a Patient table, and a Medication table.

    One doctor can prescribe many medications. One Patient can see many doctors. One patient can be prescribed many medications.

    By saying that one patient has one or more medication saves the information exactly as you want. Here is an example.

    Bob -- allergy medication
    Bob -- steroid medication
    Bob -- multivitamin

    I really hope this helps you!

    Martin

  5. #5
    Join Date
    May 2008
    Posts
    277
    I think we're getting a little wrapped around the wheel on this one.

    A Prescription is a Doctor prescribing Medicine to a Patient. Since a doctor may prescribe the same medicine to a patient multiple times, we also need to know the prescription date to distinguish one prescription from another.

    Really, this is just boils down to an intersection table between a doctor, a patient, and a medicine. The table will look something like (underlined words make up the primary key, asterisked words represent foreign keys to another table):
    prescription: doctor*, patient*, medicine*, date, dosage, ....

    A prescription of multiple medicines would simply be recorded as separate rows in the table. Some sample data may look like:
    Code:
     doctor | patient |   medicine   |    date    |     dosage
    --------+---------+--------------+------------+---------------
     Smith  | John    | Adol         | 11/14/2011 | 2 capules/day
     Smith  | John    | Augmentin    | 11/14/2011 | 1 pill/night
     Smith  | John    | MultiVitamin | 11/14/2011 | 5 ml
    This will answer your basic question of "what medicine has been prescribed and to whom?" I don't know that it's necessary to associate prescriptions with a particular visit with the doctor -- what if he gives a prescription over the phone? -- but if you want to this, then we'll need to take a closer look at your "Bill" table.

  6. #6
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by futurity View Post
    I think we're getting a little wrapped around the wheel on this one.

    A Prescription is a Doctor prescribing Medicine to a Patient. Since a doctor may prescribe the same medicine to a patient multiple times, we also need to know the prescription date to distinguish one prescription from another.

    Really, this is just boils down to an intersection table between a doctor, a patient, and a medicine. The table will look something like (underlined words make up the primary key, asterisked words represent foreign keys to another table):
    prescription: doctor*, patient*, medicine*, date, dosage, ....

    A prescription of multiple medicines would simply be recorded as separate rows in the table. Some sample data may look like:
    Code:
     doctor | patient |   medicine   |    date    |     dosage
    --------+---------+--------------+------------+---------------
     Smith  | John    | Adol         | 11/14/2011 | 2 capules/day
     Smith  | John    | Augmentin    | 11/14/2011 | 1 pill/night
     Smith  | John    | MultiVitamin | 11/14/2011 | 5 ml
    This will answer your basic question of "what medicine has been prescribed and to whom?" I don't know that it's necessary to associate prescriptions with a particular visit with the doctor -- what if he gives a prescription over the phone? -- but if you want to this, then we'll need to take a closer look at your "Bill" table.
    If I'm not mistaken, it sounds like it would be a ternary relationship. Without one piece of the puzzle, let's say medication, we wouldn't know which which doctor gave a patient a certain medication.

  7. #7
    Join Date
    Aug 2006
    Posts
    52
    futurity and Martin, thanks both of you for trying to help me
    Just one question, and I hope you will be patient with me,
    I just need to make sure I am on the right path
    Will Date attribute function as the only primary key?
    or
    Will I have to select Date, Doctor*, Medicine*, Patient* so that all of them make up a primary key?

    coz if date is the only primary key, it will duplicate, & primary keys should not duplicate
    or, would it be okay for a primary key to duplicate as long as foreign keys exist in the table? after I make sure I understand your points, I'll go ahead and start doing it in Access

  8. #8
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by Martin22 View Post
    If I'm not mistaken, it sounds like it would be a ternary relationship.
    Yes.

    Quote Originally Posted by oakeyes View Post
    Will I have to select Date, Doctor*, Medicine*, Patient* so that all of them make up a primary key?
    Yes. In Access, when you view the table in design mode, select these 4 fields and apply a primary key.

    The Doctor, Medicine, and Patient fields will also be foreign keys, referencing the primary key of your Doctor, Medicine, and Patient tables, respectively. When you create these in Access, make sure "Enforce Referential Integrity" and "Cascade Update" is selected. For Patient, make sure "Cascade Delete" is selected (leave this unchecked for Doctor and Medicine).

    coz if date is the only primary key, it will duplicate, & primary keys should not duplicate
    Not only shouldn't they duplicate, but it's impossible for them to duplicate.

  9. #9
    Join Date
    Nov 2011
    Posts
    3

    How about this data model

    ************
    *dimensions*
    ************



    doctor
    doctor id
    name
    timing
    work days
    specilization
    ph
    address


    patient
    patinet id
    name
    ph
    address
    other fields

    medicine
    medicine id
    name
    dosage

    prescription
    prescription id
    illness
    symptoms

    this is glue tabe between medicine and prescription (called as fact less fact)
    Tab ABC
    medicine id, prescription id


    ******
    *fact*
    ******

    Bill
    Bill id
    dr id
    patient id
    prescription id
    date

  10. #10
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by gautam23 View Post
    ************
    *dimensions*
    ************



    doctor
    doctor id
    name
    timing
    work days
    specilization
    ph
    address


    patient
    patinet id
    name
    ph
    address
    other fields

    medicine
    medicine id
    name
    dosage

    prescription
    prescription id
    illness
    symptoms

    this is glue tabe between medicine and prescription (called as fact less fact)
    Tab ABC
    medicine id, prescription id


    ******
    *fact*
    ******

    Bill
    Bill id
    dr id
    patient id
    prescription id
    date
    The above tables do not show relationships, rather, all of the primary keys, with respect to the other tables, are all in the Bill table. You could do something like this to make this easier to understand.


    Doctor (DoctorID, PatientID, FirstName, LastName, (what is timing?), specialization, Phone, Address,

    Patient (PatientID, FirstName, LastName, Address, Phone)

    Medication (MedicationID, PatientID, MedName, Dosage, GenericName)

    Note: The names in Bold represent primary keys, while the names in italic represent foreign keys (think of it as a primary key on vacation).

    This is just to get you started. By the way, if you want to list what days the doctor works, with respect to theory, you need to create another table for days. One doctor can work many days. See what I mean?

    I hope this better helps you see the relationships so you can create it.

  11. #11
    Join Date
    Aug 2006
    Posts
    52
    Thanks for everyone who is trying to help me, um really happy and appreciate it, and sorry for the late reply I have been busy last days,

    guatam, welcome to this forum and thanks for your model, i'll study & implement it later on and get back to it and see how it works, after I finish with this and then I'll see which one suites me ..

    currently, i created tables from scratch coz I changed to Access 2010 and I didn't want to run to problems with the old file

    I have taken screenshots of my relationship, please tell me if this is correct??
    and How do I link between table Bill and table Medication? i created a field called medication ID but do not how to link it so that I can know which bill had which Medication

    should I create an extra table that links between tblMedication and tblBill? for example a table called BillMedication and it should have the following fields:

    BillMedication
    =============
    MedicationID*
    BillID*


    and medication id should be another primary key in tblMedication???

    I mean what I need to know now, when i enter data in tblBill and data in tblMedication how will I retrieve the prescription of a specific record in table bill?

    I've told you I have little experience in this
    Attached Thumbnails Attached Thumbnails relationship1.PNG   relationship2.PNG   relationship3.PNG  

  12. #12
    Join Date
    Nov 2011
    Posts
    3
    Thanks Martin22!!

    I am new to this, as a matter of fact for any forum discussion, I've always been silent reader.

    Will keep your suggestions in mind and to all, please criticize my model and help me improve it.

    Thanks,
    Gautam

  13. #13
    Join Date
    Aug 2006
    Posts
    52
    I have been wondering, can I remove PatientID and DoctorID from table medication, and instead use BillID where it will be a primary and foriegn key? hope someone gives me an answer

  14. #14
    Join Date
    Aug 2006
    Posts
    52
    is this correct? I changed the relationship and now I think I can keep track of each visit along with its prescription,, but is this correct?? I have attached screenshots
    Attached Thumbnails Attached Thumbnails relationship4.PNG   rellationship5.PNG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •