0

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:

enter image description here

7
  • @DocBrown oh, sorry buddy. 1. No, an employee is a user on the system. But for domain control, I preferred to split the tables - so I also avoid null FK, as I mentioned in second approach above. 2. Yes, system stores the information only once, but a manager or employee only manages or works in a single company, respectively. – AD Henrique Mar 19 at 12:09
  • ... and regarding 2: so to be honest, in case a person works for two companies (which will happen in reality), there will be two records for the same person, right? – Doc Brown Mar 19 at 12:14
  • ... and question #3: I don't get why for a user with a manager role, "all other FK fields that relate to an employee will be null" - why? Managers also have an address, a salary amount, department, position (in several companies a manager is just an employee as well, but even if they own a company, those attributes exist ). – Doc Brown Mar 19 at 12:20
  • @DocBrown I edited my question and added the information we commented on here. – AD Henrique Mar 19 at 12:33
  • @DocBrown Besides, yes you are right: an employee can work for one or many companies, and a manager turns out to be an employee with information on salary, address, department, position, etc... – AD Henrique Mar 19 at 12:36
2

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.

2
  • Agree in parts. As a requirement - perhaps even with respect to Brazilian labor law - an employee who is registered with one company, cannot register with another company, until the previous contract expires. Furthermore, the application restricts that the user can only have one role (role here, it is totally linked to authorization only). Now regarding authorization verification, yes I agree. The authorization mechanism should not influence the database structure. – AD Henrique Mar 19 at 15:53
  • I updated my question with this information. – AD Henrique Mar 19 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.