Re: Hospital ERD

Well, what are your requirements for each of the staff? What will you need to do with each of the different types of staff members? And how much data do you plan to store on each.

My initial reaction is to say use a subtype construct. Have a Staff, or person table that models common things you know about a staff member (employee Id, badge number, physical attributes, etc) and then a separate table for specialized attributes about each type of staff member. I would expect it to be an incomplete subclass, in that some types of staff members might not have specialized attributes (janitors, possibly) while nurses, doctors, etc might have specialties, etc.

It is flexible, and most of all, it allows you to treat a person as special when needed, and as common when needed. If you have one table per staff member type, then you might need a bunch of relationships to model that they all get ID cards, or whatever.

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql_at_hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)

"Portroe" <fb_at_oooi.com> wrote in message
news:bvp8e4$nf$06$1_at_news.t-online.com...


> Hi all,


>

> I am constructing an ERD for a complicated hospital project at present.

> Many components are straight forward , ie. Patient, Ward, Medication

> etc.  However what I am finding difficult to cope with is how to fit the

> vast and varied staff of a hospital into an ERD,

>

> the options are have one table for Staff,(keeping things simple)

>

> or

>

> give each Type of staff member its own table, (making for a busy diagram)

>

> Has anybody got pros and cons of doing it either way,

>

> thanks

>

> Portroe

>