Database design

[Kirk]

I am creating a database that will keep up with patients and their doctors
and prescriptions. Each patient can have multiple doctors and multiple
prescriptions. What would be the best layout for this scenero?

I have tried the following, but gets to complicated.

Patient information table


doctor information table


patient script1 table

patient script2 table

patient script3 table


some patients can have up to 13 prescriptions, and keeping all the records
together is not working. I am using Access 2003 Inside Track for a reference
book.

Thanks in advance for your guideance.

Kirk

 

[Marc]

I am creating a database that will keep up with patients and their doctors
and prescriptions. Each patient can have multiple doctors and multiple
prescriptions. What would be the best layout for this scenero?

I have tried the following, but gets to complicated.

Patient information table


doctor information table


patient script1 table

patient script2 table

patient script3 table

hi,
You don't need a separate table for each script - 2 tables should do

Visit table
visitid - autonumber
doctorid - from doctor table
patientid - from patiend table
visitdate - date of appt

Script table
scriptid
script stuff- ...
visitid - duplicates ok - if more than one script per visit.

or thinking again just
Script table
scriptid
script stuff- ...
patientid - duplicates ok - if more than one script per visit.
doctorid - duplicates ok - doc scripts for more than one patient

HTH
Marc

 

[Albert D. Kallal]

Here is a sample ER diagram...

http://www.databaseanswers.com/data_models/pharmacies_and_prescriptions/inde
x.htm

 

[John Nurick]

Hi Kirk,

By the sound of it you have something like the following entities:

Person (can be a doctor, a patient, or both -
i.e. a doctor who needs a prescription)
PrescribableItem (a drug, appliance, etc. that a
doctor can prescribe for a patient)
Consultation (an encounter between a patient and a
doctor from which zero or more Prescriptions may issue)
Prescription (one or more PresriptionItems prescribed
by one doctor for one patient at one Consultation)
Repeat (each Prescription may include zero or more repeats)
PrescriptionItem (one PrescribableItem in one Prescription,
with quantity, dosage, etc.)

If this is a database intended for actual use (as opposed to a learning
exercise), you should probably also consider
-providing for information on patients' allergies
and adverse reactions
-linking into health service/HMO/insurers' databases
for information about PrescribableItems and
pricing
and you must make sure your design and implementation comply with your
national and regional regulations on data protection, patient
confidentiality, and healthcare provision.