How can I model a medical scenario in an entity-relationship diagram?

Question

I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book.

In this way, I have started creating an entity-relationship diagram (ERD), for a medical scenario, that is shown bellow:

I am not quite finished as I am having some issues converting some of the relevant business rules into ERD notation. Also, when reading the business rules concerning the associations keep in mind that the entity types Physician, Order, Diagnosis, Treatment, and Patient are needed (because in the book they are stated as business rules and I would like to understand that specific model), and although they could probably be modeled in a different way, I am trying to understand this specific model.

Business scenario description

• Physicians diagnose any number of Conditions affecting a Patient, and a Diagnosis may apply to many Patients.

• The involved hospital records the following information: date and time of diagnosis, diagnosis code, and description.

• Physicians may order and perform any number of Services and Treatments for a Patient, or may not perform any Treatment.

• A Treatment or Service may be performed on any number of Patients, and a Patient may have Treatments performed or ordered by any number of Physicians.

• For each Treatment or Service rendered, the hospital records the following information: Physician ordering the Treatment, treatment date, treatment time, and results.

Entity Type Definitions

• Patient. A person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, the medical record number (MRN), and a name.

• Physician A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID and name.

• Diagnosis. A patient's medical condition diagnosed by a physician. Each diagnosis has a diagnosis has a diagnosis ID/code and diagnosis name. Example: 1032, STAPH FOOD POISIONING.

• *Treatment. Any test or procedure ordered by and/or performed by a pysician for a patient. Each treatment has a treatment ID/treatment code and treatment name using standard codes. Procedure codes are used for diagnostic and therapeutic procedures. Example: 1244, Chest X-Ray.

• Order. Any order issued by a physician for treatment and/or services such as diagnostic tests (radiology) and therapeutic procedures (physical therapy), or drugs (perscriptions). Each order has an order ID, order date, and order time.

In this manner, I was wondering if someone could look at this and derive the same ERD from the business rules I have or if I messed up the translation.

My considerations

• The specific issue I can not seem to wrap my head around is the existence of multiple relationships between the entity types.

  First of all, I do not even know what kind of cardinalities to put on regarding the relationship between Order and Treatment.

• Another issue for me is the multiple relationships between Patient, Diagnosis and Physician. Also, the same issue with Physician, Patient, and Treament.

  Are these perhaps suppose to be ternary degree relationships, and (or) contain weak entity types to make this more understandable? Like Order and Treatment, or Treatment and Diagnosis I could see one depending on another as a Treatment in my mind would depend on the Diagnosis (correct me if I am wrong).

Answer 1

Consideration about multiple relationships between entities

In real life, Patients and Physicians interact in several ways. The most obvious one is that you go to see your doctor when you're ill (that's one kind of relationship between Patient and Physician). But you, as a Patient, may refer one Physician to a friend of yours (who is also ill, and, as such a Patient), because you liked how it works. This is a ternary relationship involving two Patients (the referer, and the refered) and one Physician.

Non-binary relationships

Not all relationships are between two tables (such as in the previous case). Another instance: a Diagnosis is performed by a Physician to a Patient (and, you could add: at some time, in some place, after x, y and z tests, etc. as attributes to this relationship). Having one ternary relationship is not the same as having two relationships Physician makes Diagnostic and Patient diagnosed Diagnostic. It you ever only visited one doctor, no doubt about who diagnosed. As soon as you visit two doctors... who diagnosed what is much less clear.

---

This is not answering all your questions, but hope it helps.

Answer 2

For Orders and Treatment, the relationship appears to be that a Treatment is sort of a generic description / code / entity describing a what can be done, while an Order is what Will be or was done and to whom. There could be 0 to many treatments ordered.

So your doctor Orders a Treatment for a Patient with the various time/date attributes. The order links your patient to your doctor and to the treatment option the doctor requests. It would have columns like: OrderID (PK), PatientID, DoctorID, TreatmentID, OrderDateTime, TreatmentStartDateTime TreatmentCompleteDateTime, etc.

So your ERD should show an Orders table at the center connecting out to Patients, Treatments, and Doctors tables.

---

A patient can have 0 to many Diagnoses. But a diagnosis must be assigned by a doctor. So you won't have a Diagnosis column under patient. Instead, you'd have a Patient-Diagnosis table to link patients to diagnoses and to doctors. This would list the PatientID, DiagnosisCodesID, DoctorID, and whatever time/date values might apply (prelim date, confirmed date, etc.).

Your ERD should have a Diagnoses table at the center, connected out to Patients, DiagnosisCodes, and Doctors table.

Does this help answer your questions?

Answer 3

This is not a complete answer (as more details are needed), but based on your description, I'd say that:

1. the relationship between [Physician]and [Order] should be 0/1 to many because each order is unique in terms of orderid.
2. the relationship between [Order] and [Treatment] is 0/1 to many because one order may need multiple treatments to fulfill.

Answer 4

You may need to introduce additional Entities that aren't explicitly called out in the Business Rules to make your model work. This is a normal part of translating requirements into a model.

Typically in this domain you would have an Entity that represents an Episode-of-Care. In a Hospital, this might be called an Admission, but more generally it's the thing to which contains a set of Diagnoses, including a Primary Diagnosis, a set of Orders, Procedures, etc.