How to model a medical scenario in an entity-relationship diagram

database-designerd

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:

ERD

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).

Best Answer

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.

Related Question