Erd

Hello everyone,

I have modelled Entity relationship diagram (logical design) for a database that works with hospital environment. I wonder weather I have normalised data or not.

The Design can be viewed or downloaded from: URL

Regards,
Tony Radoni

It is normalized. However for any given event_id, you'll need to visit up to 6 tables to find who was involved with the event.

Personally, I would replace tables Nurse, Anaesthetist, Consultant, Doctor and Surgeon with just one table:
Personnel
------------
Personnel_id primary key
Personnel_type tinyint (1 = Nurse, 2 = Anaest., 3= consultant, 4 = doctor, 5 = surgeon)
Last Name
First Name
On_Call_Tel
etc.


Then you can replace tables Nurse_event, Anaesthetist_event and Surgeon_Event with one table:
Personnel_Event
-------------------
ID primary key
Personnel_id
Event_id


-Matt.

I generally agree with madmatt75, although there is really no need to force an attribute like Personnel_type into an INT or TINYINT. (Yes, there is a slight performance gain on very large databases, but there is a corresponding loss of data readability--let the data speak for itself as much as possible). Also, you might want an external "validation table", detailing the available Personnel types, in which case the Personnel_type column has a foreign key lookup to the validation table. (This foreign key can be INT or it can be a VARCHAR, as I mentioned for readability).

lOOKS PRETTY GOOD TO ME.

PS WHAT DID YOU USE TO CTEATE YOUR ERD