Recursive Relationship in ER Diagram

[kay21]

Hi all,

I need some help with defining a recursive relationship in a ER diagram. It is a part of my assignment and the below is quoted in the case study.

Admin staffs are in-charge admitting inpatient to the hospital.
Several nurses work in each ward and one of them is in charge of the ward.

I have created entities with attributes as below but I'm not sure if it is correct

NURSE (Nurse_ID, Nurse_In_Charge)
WARD (Ward ID, Ward_Name, Main_Treatment, Nurse_In_Charge, Number_Of_Bed)

I have also attached the ER diagram for the recursive relationship with this post, please have a look and comment.

Thank you.

[debasisdas]

Remove Nurse_In_Charge from WARD and maintain a separate table for day to day activty of the ward.

There you can maintain ward ward_id, nurse_id, date_of_activity etc.

[kay21]

Thank you debasisdas. So my entities will look like this;

NURSE (Nurse_ID, Nurse_In_Charge)
WARD (Ward ID, Ward_Name, Main_Treatment, Number_Of_Bed)

Should I have Nurse_In_Charge in the NURSE entity? I thought of removing it and replacing it with Department.

Is my ER diagram for the recursive relationship correct?

Remove Nurse_In_Charge from WARD and maintain a separate table for day to day activty of the ward.

There you can maintain ward ward_id, nurse_id, date_of_activity etc.

[debasisdas]

What you plan to store in Nurse_In_Charge, because you already have a Nurse_ID column.

I would suggest make that a flag (store 0 or 1) depending up on if the particular person can be In charge or not.