Medical Clinic Notes:

You are designing a database for a medical clinic that patients can go to for particular procedures. The patient must come with a referral/request from a doctor for the procedure. If the patient does not have a doctor, they can make an appointment with a doctor at the clinic prior to admission. Remember, these are only rough overview notes for the purpose of showing an example of a relational database. Note that the key on each file is underlined.

Information that must be included in the system:

Patient information
Contact information for the patient
Insurance information for the patient
Information about insurance companies and insurance plans
Patient allergy information
Information about allergies
Referral information
Consultation information
Doctor information
Information about the procedure the patient is having
General information about the procedure
Medical prescribed to the patient
Information about medicines

Design Notes:

Patients must have information on the patient file/table/store at the clinic:

Patient: (Patient id#, name, address (work and home), phone (work and home), date of birth, sex)

Note: the patient id # could be called patient medical record #

The patient needs to also provide information about who should be contacted in an emergency. The clinic requires multiple contacts with a ranking number. Therefore the data cannot be carried on the patient file/table/store but on a separate file with a relationship through the patient id#.

Contact: (Patient id #, rank, name, address (work and home), phone (work and home), relationship to patient, comments needed to facilitate contact)

The patient hopefully has insurance and in fact they may have more than one insurance. In that case, there is usually a primary insurance to bill and secondary ones to bill if first does not cover everything.

Patient Insurance: (Patient id#, ranking for billing, insurance id#, plan #, policy#, policy information)

There would then be an insurance file that can be linked to by the insurance id# that will carry information about the company. The plan # will link to the insurance plan file that will carry information about the plan.

Insurance: (Insurance id#, insurance name, address, phone, contact etc.)

Insurance Plan: (Insurance id#, plan #, plan information)

A patient might also have allergies, we need to carry that information. Since a patient can have multiple allergies this is a one to many relationship. We will carry specific problems that the patient had with the allergy on this file. We will also have the ability to link to an allergy file that will contain general information about the allergy).

Patient Allergy: (Patient id#, allergy code, specific problems for this patient)
Allergy: (Allergy code, known problems, know effects)

We said at the beginning that the patient usually comes with a referral. We need to carry information about that on a referral file/table/store.

Referral: (Patient id #, referral doctor id #, date of referral for condition, condition code)

If the patient does not come with a referral or if the referral requires more information, the patient consults with a doctor at the medical clinic.

Consultation: (Patient id#, date of consult, time of consult, doctor id#, diagnosis)

If the possibility exists of the patient consulting with two doctors on the same date than the doctor code would also be carried as part of the key.

The doctor file for doctors that work at the medical clinic is:

Doctor: (Doctor id#, doctor name address, extension, home phone, pager, schedule)

The patient is being admitted for a procedure so we need to carry information about the specific procedure on the patient procedure file. A patient can have a procedure more than once so this is in a one to many relationship with the patient file.

Patient Procedure: (Patient id#, procedure id#, date of procedure, admit time, discharge time, admit doctor code, admit diagnostic, notes on patient relative to procedure)

There will also be a procedure file that contains information about the procedure.

Procedure: (Procedure id#, equipment needed, average duration, other information about the procedure)

Clearly there should also be a schedule that is kept for equipment and doctor's time. I have not included that in this database.

It is frequent that medicine is prescribed in association with the procedure. Since multiple medications can be prescribed we need a one to many relationship with the patient, the procedure and the date of the procedure.

Medicine Prescribed: (Patient id#, procedure id#, date of procedure, medicine code, dosage, days prescribed for, doctor prescribed restrictions, comments about prescription)

A medicine file will also be kept that will give information about the medicines being prescribed.

Medicine: (Medicine code, information about the medicine to help doctor prescribing)