I am building an application for employee management. To start discussion, I have 3 roles: admin, manager and employee
- admin does everything
- manager owns a company and manages its employees.
- employees only record their point and nothing more.
My tables are:
- users (store authentication and permission information)
- companies (all company information that a manager has)
- employees (information of an employee such as address, salary amount, department, position, etc...)
- All people are users in the application: admin, manager or employee. Admin can see and do everything, Manager only sees and edits information about your company. The employee sees and edits only your information as well.
- For domain control I splitted the user table into two: users and employees. So I guarantee that some FKs are not null, as for example position_id, department_id, etc... (some more details about, in the approaches below)
- A user with Manager role, manages only one company.
- A user with Employee role can work for one or more companies.
- With regard to Brazilian labor law, an employee who is registered with one company,
cannotregister with another company, until the previous contract expires.
I've tried some approaches but I always fall into the same result: null information.
- Create a FK
company_idwithin the users table, so we define which company a given user (manager) has. But if that user is not a manager, this field becomes null.
- Merge the user and employee tables, but now if I am a user with manager role, all other FK fields that relate to an employee, will be null.
- Create an associative table with a unique key constraint, between users and companies. (The most interesting so far). However, even so, if I am a user with manager role, this table must be filled out.
So, based on this scenario, what would be the an approach which avoids these problems?
See my last drawing for the third approach mentioned above: