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