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

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

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:
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.

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

Yes.

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

Not only shouldn't they duplicate, but it's impossible for them to duplicate.

************
*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.

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

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

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

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