Hi,

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

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.

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

thank's

do not believe everything you read on the internet