Database design to model companies and many roles

Question

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...)

Some requirements:

1. 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.
2. 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)
3. A user with Manager role, manages only one company.
4. A user with Employee role can work for one or more companies.
5. With regard to Brazilian labor law, an employee who is registered with one company, cannot register with another company, until the previous contract expires.

I've tried some approaches but I always fall into the same result: null information.

Ie:

1. Create a FK company_id within 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.
2. 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.
3. 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:

Answer 1

First of all, avoiding NULL values for foreign keys seems like an arbitrary goal. If you follow it through to the extreme, it may lead you to complicated solution without bringing much value.

The other point is that it's often best to keep any role-specific data out of the User data. Users of your system may have account names and passwords, possibly real-life names and contact information, maybe home addresses (although that is already unlikely to matter outside their relationship to their roles within companies).

Roles are the place where you can store information about users that matter within some context, in your case within the context of companies. Users may work for two different companies with different work phone numbers, for example. They might even work for one company as a manager while working as a normal employee for another. So the Role table has a foreign key user_id, a role or function name, and possibly role-specific data. It also has a foreign key to refer to Company, and that's the place where the NOT NULL constraint may be not helpful. The admin role within your system is not bound to a company, only to a user, so restricting the company foreign key to be NOT NULL would force you to represent the admin role using a different mechanism, such as a flag in the User table, or a separate table for admin roles that does not have a company_id foreign key.

What information can be seen and managed by a logged-in user should be completely independent of the table structures. The authorization checking function would take as input the roles that the user has, and the requested functionality, and then return whether the user is allowed to execute that function. The authorization mechanism should not influence the table structure representing your domain data.