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
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.
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
Another issue for me is the multiple relationships between
Physician. Also, the same issue with
Are these perhaps suppose to be ternary degree relationships, and (or) contain weak entity types to make this more understandable? Like
DiagnosisI could see one depending on another as a
Treatmentin my mind would depend on the
Diagnosis(correct me if I am wrong).