CODEWITHSUNDEEP
databasedatabase-designentity-relationship
idontknow
idontknow

Reputation: 3

Modeling a database using entity relationship diagram

I've created this ERD, take a look: https://i.gyazo.com/ea3ad2073ad5302834b642dea7467662.png

But now I want to add some more information to my diagram, but I do not how I should do that.

I want to modify my previous ERD, I need to ensure that every single shift is delivered to the hospital where the doctor works. Also, I need to register the Medical Insurance of each patient.

Hope you can help me

Upvotes: 0

Views: 781

Answers (1)

reaanb
reaanb

Reputation: 10084

You can model the Works at relationship in your ERD, but complex constraints like ensuring that a doctor's shifts occur at the hospital they work at can only be properly represented in the physical schema since it relies on a composite FK constraint.

I would make a note on my ERD diagram to indicate the constraint:

Doctor works at Hospital ERD

Then, in the physical schema:

CREATE TABLE Works_At (
    doctor_dna VARCHAR NOT NULL,
    hospital_code VARCHAR NOT NULL,
    PRIMARY KEY (doctor_dna),
    UNIQUE KEY (doctor_dna, hospital_code)
);

CREATE TABLE Shifts (
    shift_id INT NOT NULL,
    doctor_dna VARCHAR NOT NULL,
    patient_dna VARCHAR NOT NULL,
    hospital_code VARCHAR NOT NULL,
    date DATETIME NOT NULL,
    hour INT NOT NULL,
    PRIMARY KEY (shift_id),
    FOREIGN KEY (doctor_dna, hospital_code) REFERENCES Works_At (doctor_dna, hospital_code)
);

Note the UNIQUE KEY in Works_At and the composite FOREIGN KEY constraint in Shifts.

Some comments on your diagram: it's redundant to use both Crow's feet and 1-N cardinality indicators. I would advise only 1-N cardinality indicators since your diagram mostly uses Chen's notation. Crow's feet are more commonly used in table diagrams.

Second, I believe Shifts is incorrectly modeled as a weak entity set. Weak entity sets are entity sets that are (at least partially) identified by a single parent entity set. If your intent is that Shifts are uniquely identified by Patient, Doctor and Hospital, it would be better represented as a ternary relationship between those entity sets. However, in my experience, patients aren't limited to a single visit per doctor / hospital. Perhaps you meant to use Date and Hour as weak keys, but that makes for a complicated relation, and if you ever wanted to associate anything else with shifts, you would have to include all 5 columns as a composite foreign key in another table. Personally, I would rather introduce a surrogate key to make it a regular entity set.

If might be a good idea to do the same for Person. There are numerous problems with using DNA as an identifier. Size (DNA profiles tend to be large), privacy concerns (identifiers could contain valuable or sensitive information), practical difficulties (must every doctor and patient undergo DNA sequencing before the can be registered?), accuracy (limited profiles may not be guaranteed unique while full profiles would be impractical).

As for medical insurance, I suggest you study the topic online (or even consult with an expert in the field) and determine what entities, attributes and relationships you require. Once you know what information you want to add, I can advise on how to add it.

Upvotes: 1

Related Questions