E-R Diagrams 04: Medical Clinic

For each entity (table), create a list of attributes (fields). Underline the key field. Italicize any foreign keys.

Key question here is this: Are appointments and visits different entities? Or can they be the same type of entity with a single field indicating whether the appointment was kept? My solution is to have a single entity called APPOINTMENT that has a field indicating whether the appointment was kept (thereby making it a visit). I have not included functional dependencies in this document.

Possible Entities:

Relationships:

Entities and attributes:

·        APPOINTMENT: AppointmentID, PatientID, DoctorID, Date/Time, Kept, DiagnosisID, BillAmount

NOTES: When deciding on the attributes of each entity, I decided to combine DIAGNOSIS and APPOINTMENT. If I didn't do that, I would have to create a key to identify appointments (either an AppointmentID field, or a composite key made up of PatientID, DoctorID, Date, and Time). I considered it easier to combine DIAGNOSIS with APPOINTMENT.

APPOINTMENT is an association table (an intersection table with fields other than the two intersecting foreign keys) so I included it as a separate entity.

TREATMENT and DIAGNOSIS are both considered to be large (standard) lists of codes and descriptions (basically dictionaries). An APPOINTMENT can have many TREATMENTs and a TREATMENT can have many APPOINTMENTS. This will yield an intersection table combining the keys of the APPOINTMENT and the TREATMENT. A DIAGNOSIS can be made for many APPOINTMENTs (many people may be diagnosed to have the flu), but there will only be one DIAGNOSIS per APPOINTMENT (this may or may not be true in real life—I'm not a doctor).

Since there is one bill per appointment, we could (probably should) add it to the APPOINTMENT entity.

There will be a N:M relationship between APPOINTMENT and PAYMENT. This is probably the trickiest part of this design. A PAYMENT can be split up to pay for multiple APPOINTMENTs. And an APPOINTMENT can have many payments spread out over time. However, simply linking the PAYMENT and APPOINTMENT tables together in a pure intersection table does not work. We have to have a way to indicate (in the case of a split payment) how much goes for each APPOINTMENT. The only way to know if this is correct is to look at some examples.

Assume we have appointment #1 and appointment #2. The bill for #1 is $1,000 and the bill for #2 is $500. Payment #1 is $200, with $100 for appointment #1 and $100 for appointment #2. This would create two entries in the association table between APPOINTMENT and PAYMENT. Payment #2 comes in a week later, again with $100 for appointment #1 and $100 for appointment #2. This is what the entries would look like in the association table:

PaymentID

AppointmentID

Amount

1

1

$100

1

2

$100

2

1

$100

2

2

$100

The questions you need to ask are: Can I find out how much of which payment went to pay off the bill for which appointment? Can I find out when the payments were made? Will my keys always be unique? I don't think there are any other relevant questions, and both of these questions can be answered, although a join with the PAYMENT table is required to find the date of the payment. Regarding unique keys: Is it possible for there to be another entry with the key 1,1? Or with the key 1,2? Or 2,1 or 2,2? If there is ever a duplicate key, that means that the same payment was applied to the same appointment TWICE. Example: 1, 1, $100 and 1, 1, $200. This means that payment #1 had $100 go to appointment #1 and it also had $200 go to appointment #1, so this would just be: 1, 1, $300.