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 or they must have a consultation. If the patient does not have a doctor, they can make an appointment with a doctor at the clinic prior to admission for a consultation.. Remember, these are only rough overview notes for the purpose of showing an example of a relational database. A medical clinic in the "real world" should have a far more sophisticated system.

Information that must be included in the system:

Patient information (including name, address (both home and work), phone (both home and work), email, date of birth, sex
Contact information for the patient - can be multiple (including name, address (both work and home), phone (both work and home), relationship to patient, comments needed to facilitate contact)
Insurance information for the patient - can be multiple (including ranking for billing, insurance id#, plan #, policy #, policy information)
Information about insurance companies and insurance plans (including insurance name, address, phone, contact etc, plan no plan information)
Patient allergy information (including specific problems for this patient)
Information about allergies (including know causes and effect)
Referral information (including referring doctor and information about contacting the referring doctor, date and condition)
Consultation information (including date, diagnosis, comments)
Doctor information (including doctor name, address, phone etc and schedule)
Information about the procedure the patient is having
General information about the procedure
Medicine prescribed to the patient (including date, dosage, length, doctor, comments etc)
Information about medicines

Design Notes:

The structure that I am using will have the table name followed by the columns in parenthesis. Note that the primary key on each table is underlined.

The clinic will set up information for each patient that comes to the clinic. This information will be stored on the patient table. The information on the patient row (record) on this table will all be directly related to the patient. Note that I have simply said address, the analyst will need to decide whether to break this information down into separate columns for street address, apartment, city, state, zip. This is very limited information so the patient information should probably be expanded in a professional environment. Note: the patient id # could be called patient medical record #

Patient: (Patient idno, name, address (work and home), phone (work and home), email, date of birth, sex)

I have now established a table for the patient - all of the other Information that must be included in the system

The patient needs to also provide information about who should be contacted in an emergency. The clinic asks for multiple contacts with a ranking number to tell the order the clinic should use in trying to contact the person in case of an emergency. In practical terms, this frequently means a spouse or parent will be ranked first and another relative or a friend will be ranked next. Because multiple contacts are requested the data cannot be carried on the patient table because that would result in repeating groups. There is a one to many relationship between patient and contacts. I will develop a separate table with a link through the patient idno. Patient idno on the contact table will therefore be a foreign key linking to the patient table. Because multiple contacts are requested, the patient idno cannot serve as the primary key. I am going to combine it with rank because every patient will only have one contact with a ranking of 1, one contact with a ranking of 2 etc. All of the information on this table relates directly to the combined key of patient idno and rank, there are no repeating groups and there are no determinants.

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

The patient may have insurance and in fact a 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. I need to carry all of the insurance information and since there is a one to many relationship between patient and patient insurance I would have a repeating group if I tried to carry patient insurance information on the patient table. Therefore I will develop a patient insurance table with patient idno acting as a foreign key to link back to patient. Patient idno will also be part of the primary key but since there can be more than one row (record) for each patient I will need to combine the patient idno with another column to create a primary key. Again ranking appears to be a good choice because the insurance must be ranked by primary insurance and other insurances to be used for additional billing.

Patient Insurance: (Patient idno, ranking for billing, insurance idno, plan no, policy no, policy information)

I cannot carry information about the insurance company in the Patient Insurance table because the insurance idno is a determinant for that information and carrying it would break the rules of normalization. Practically speaking it also would make no sense to carry insurance information over and over again for every patient using a particular insurance company. Think of the maintenance issues when the phone number changes.

There would then be an Iinsurance table to carry this information that can be linked to the Patient Insurance table by the insurance idno. This table will carry information about the company. The contact information that I will carry on this table would reference general information, not necessarily plan specific information.

Because an insurance company can have many plans, I cannot carry plan information on the Insurance table or I will have repeating groups. The relationship between plans and the insurance company is a one to many relationship. Therefore, I need to develop a separate table for plan information. The plan no on the Patient Insurance table will link to the Insurance plan table that will carry information about the plan. I can also carry contact information on the plan table that would be a contact person at the insurance company that is a specialist in that particular plan.

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

Insurance Plan: (Insurance idno, plan no plan contact, plan information)

Now lets stop for a minute and look at the relationships we have established relating to insurance.

Patient: (Patient idno, name, address (work and home), phone (work and home), email, date of birth, sex)

Patient Insurance: (Patient idno, ranking for billing, insurance idno, plan no, policy no, policy information)

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

Insurance Plan: (Insurance idno, plan no plan contact, plan information)

Table	Primary key	Foreign key
Patient	Patient idno
Patient Insurance	Patient idno + rank (concatenated or composite key)	Patient idno links to Patient table Insurance idno links to Insurance table Insurance idno + plan no link to Insurance Plan table
Insurance	Insurance idno
Insurance Plan	Insurance idno + plan no (concatenated or composite key)	Insurance idno links to Insurance table

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 and we cannot carry it on the patient table or we will break the normalization rule that specifies no repeating groups. We will carry specific problems that the patient had with the allergy on a Patient Allergy table that we develop. We will also have the ability to link to an allergy table that will contain general information about the allergy. This could be a table that can be purchased and would therefore have specific codes that we would have to use in specifying the allergies.

Because the patient can have many allergies, the primary key in the Patient Allergy table is the Patent idno plus the allergy code. This can be called a concatenated or composite key. A concatenated or composite key can be defined as two data elements used together to form a key. The allergy code is also a foreign key to the Allergy Table, which contains information about the allergies. I have not dealt with the structure of the allergy table and in fact have left it as comments. A more sophisticated structure is probably warranted but is outside the parameters of this discussion.

Patient Allergy: (Patient idno, allergy code, comment on specific problems for this patient)

Allergy: (Allergy code, known problems, know effects)

Table	Primary key	Foreign key
Patient	Patient idno
Patient Allergy	Patient idno + allergy code (concatenated key)	Patient idno links to Patient table Allergy code to Allergy table
Allergy	Allergy code

We said at the beginning that the patient usually comes with a referral. We need to carry information about that on a referral table. Over the course of time, a patient might be referred by multiple doctors for multiple conditions so we need to assume that there is the potential of a one to many relationship between patient and referral. In that case, we can make the patient idno and the referral doctor idno the key. However we also need to consider the fact that the same doctor may refer the same patient twice for different conditions. We need to make a decision about the emphasis of this table. We can consider making the referral id a combination of patient idno and condition code. We also run into the possibility that the patient might be referred at different times by different doctors for the same condition. There are several possible ways this table could be constructed and a clear understanding of the purpose of the table for the management of the clinic is critical to making this decision. Two possibilities including date in the key are shown below. Note that the primary keys that I have created here are concatenated keys or composite keys.

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

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

We now need a table for condition code where we can put more information about the medical condition.

Condition Table: (Condition code, name of condition, usually procedure recommended etc)

Again, I am not even coming close to the information that would have to be carried about a condition. Whole medical encyclopedias are written about this. I am taking a very simple approach that is not realistic, but works within my constraints.

In either of these cases we need a Doctor Referral table that will contain information about the doctor so that they can be contacted

Doctor Referral Table: (Referral doctor idno, name, address, phone, email, specialty etc)

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. A patient can have more than one consultation so it is not sufficient to carry just the patient idno as the primary key. I need a composite or concatenated key composed of the patient idno and something else to make it unique. I have chosen to use the date of consult. This information must be captured and recorded on a table.

Consultation: (Patient idno, date of consult, time of consult, doctor idno, condition code, diagnosis)

Other things to consider: 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. If you want the flexibility of having a consultation result in multiple condition codes for the patient then condition code would become part of the key. Again, I am trying to limit the scope of the problem so I am going to leave the table as shown above.

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

Doctor: (Doctor idno, doctor name address, extension, home phone, email, pager, schedule)

In fact schedule is another area where on closer examination more information will probably be needed. Again, given the scope of this project, I have one field for schedule on the doctor table.

The patient is being admitted for one or more procedures so we need to carry information about the specific procedures on the patient procedure table. Note that the relationship between patient and procedure is a many to many relationship. One patient can have many procedures and one procedure can be performed on many patients. The Patient Procedure table is really a bridge file between the Patient table and the Procedure Table. It is also a concatenated or composite key because you need to combine several data elements to get a distinct primary key.

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

There will also be a procedure table that contains information about the procedure. Again, in a real clinic situation far more complexity would be involved.

Procedure: (Procedure idno, average duration, other information about the procedure)

Medicines may also be prescribed and a record should be kept. Since the patient can be prescribed many medicines the medicine code is part of the key and since the same medicine can be prescribed multiple times we are going to keep the date prescribed as part of the key as well. This structure has created a composite or concatenated key. The patient idno is a foreign key back to the patient table and the medicine code is a foreign key to the medicine table.

Medicine Prescribed: (Patient idno, medicine code, date prescribed, dosage, days prescribed for, doctor idno, 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)