I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c.

Is it better to create another table for doctors,one for nurses,etc or should I connect the appointment table, nurse_schedule, test_table, all directly to the staff table ??

  • From the very nature of the question, you do not have the experience to design a database for this task. If this is the US, the database design is highly affected by HIPAA regulation and needs someone with a minimum of ten years of database design to do the design effectivly given the very serious legal constraints on the system. Other countries would have other leagal requirements. If you had that experience you would never have asked this question which is database 101. This is not a design job for an amateur. You can get your company is serious legal trouble. – HLGEM Dec 14 '11 at 18:34
  • this is a college project work ... you're right i d'ont have much experience designing databases, but at least you could help ... – nkvp Dec 15 '11 at 2:31

I think: general to general, specific to specific. If you are planning to add some table and this data is specific to nurses ONLY, so make reference to nurses. If it is a permissions list, use reference to general staff table or better users list. Generalization is a way to organize that we want to organize. In life it's more complicated.

enter image description here

  • but suppose the hospital is big.. how do we add new types of staff (who have different permissions) if we create a table for each specialization (doctor, nurse,etc) ... – nkvp Dec 13 '11 at 11:37
  • @nkvp link staff(general) to permissions, because of all have permissions (doctors, nurses...) – garik Dec 13 '11 at 12:00
  • 3
    A link table does not need a surrogate key... – gbn Dec 13 '11 at 20:30
  • YOu only need new more specif tables if you have data that needs to be stored about them. Nor do new types of staff come along frequently. If they do and you have new functionality for them ,then you wil of course need to build the db strucure to handle it just like you would need to build any application pages to handle what they do. – HLGEM Dec 14 '11 at 18:37
  • @garik in the diagram, in the staff to doctor and staff to nurse connections, there seems to be two primary keys on both sides of the relationship and no foreign key. how do we connect these? – nkvp Dec 15 '11 at 12:02

First of all, if this is homework, please tag it as such.

Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand those requirements. If you don't completely and clearly understand the requirements, your schema is going to be miles off the mark.

And, to actually answer the question (at least as best I can with the information given), I'd keep the staff in one table, however you may want to create ancillary tables for each 'type' of staff member (doctor, nurse, janitor, admin, etc) to store data specific to that staff-member-type. To give an answer that's any more specific, the requirements need to be more specific.

  • no.. this is not homework.. this is a project (as stated above). i think you got the question.. exactly what i was thinking of. however, in that case, is it better to keep a common id for all staff for generality or should we have separate ids for doctor table, nurse table, etc e.g because a diagnosis can only be done by a doctor (doctor id can be used in the diagnosis table and not staff id). – nkvp Dec 13 '11 at 11:25
  • One more doubt... Suppose the hospital is big.. how do we add new types of staff (who have different permissions) if we create a table for each specialization every time (doctor, nurse,etc) (sorry for the repetition below) – nkvp Dec 13 '11 at 11:41
  • 1
    @nkvp: If only a doctor can make a diagnosis, then the table that stores diagnoses should have a foreign key to the doctors table, not to the staff table. You don't need a different id; you just need the right foreign key. The right person to design a hospital system would already know that. (That's an observation, not a criticism.) – Mike Sherrill 'Cat Recall' Dec 13 '11 at 23:53
  • kk..i know that... i was just asking if there is another way ... – nkvp Dec 14 '11 at 8:34
  • 1
    For that, I'm going to fall back to "it depends". If you have a clear idea of what's going on and what your application needs to do, then yes, pre-define everything. If you're floundering around in the dark with vague guidelines, then you need more flexibility. – Simon Righarts Dec 15 '11 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.