Supervisor and assistant relationship

Discussion created by TimothyRamos on Dec 9, 2016
Latest reply on Jun 22, 2017 by TimothyRamos

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