I have a small Speech therapy clinic and I have implemented a solution to track therapy notes and billing. I would like to roll out a solution to track employee relationships (supervisor, assistant). The nature of the relationship is as follows.
1: A supervisor may perform therapy autonomously
2: An assistant must work under a supervisor's license to perform therapy
3: Both supervisor and assistants are employees
4: the supervisor receives a percentage of each hour of therapy performed by their assistant
My BIG questions: should all employees exist on a single table? Or, given their relationship should they exist on separate tables?
Follow up questions
1: If employees are on different tables, should I have separate contact table for employees, business contacts, clients for the purpose of company emails news letters?
2: If they are are in a single table, how do I create the relationship between different types of employees (e.g. supervisors, assistants)? Should I use the
Primary Key: PK_empID
Foreign Key(s): FK_supervisorID ; FK_assistantID
EMPLOYEE Table EMPLOYEE TABLE (SUPERVISOR) EMPLOYEE TABLE (Assistant)
Primary Key: PK_empID -------- FK_supervisorID