Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007

    staff, staff type, and roles (was "need some help")


    i need some help on some problems regarding database design that i build right now.
    currently, i'm doing my project and at the phase for designing the effective and efficient database for small clinic. it has several job type for the users, such as admin, receptionist, pharmacy, doctor, etc.
    previously, i create a table called STAFF as a supertype and admin etc as subtypes because they're sharing some similar attributes but also has unique attribute to one another. my question is:
    1. is it good to have such supertype-subtype design for database? (in term of efficiency and effectiveness). because when i search it in internet, some journal says that supertype and subtype should be avoided.

    2. if it's not, then how i encountered the problem? should i create only 1 single table called STAFF which has an attribute job_type, in which job_type will be defined in another table to identify the job description (1=doctor, 2=admin, 3=pharmacist, etc). if that so, then how to differentiate the roles for each job_type? For example: doctor is the one who can treat the patient while the other's are not allowed, and receptionist have the ability to assign an appointment with the patient

    i reaaly appreciate your help and thank's for you attention

  2. #2
    Join Date
    Aug 2006
    Northern KY
    Sounds like you are starting an authorization system. In healthcare, these are never simple.

    Always separate USER (person) from ROLE (allowable duties) and provide for multiple ROLEs per person. Roles then consist of allwable functions.

    Suggest you find an off-the-shelf design for a "role based authorization" system and adopt it.

  3. #3
    Join Date
    Feb 2007
    thanks for your suggestion, i've been browsing through the internet to find "role based authorization" and it's good enough.


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by tenma-tenma
    ...when i search it in internet, some journal says that supertype and subtype should be avoided.
    do not believe everything you read on the internet | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts