-2

I need to store user data for both employees of my company, and for users of the application. As I am storing the company details anyway, and have a M-M connection between users and companies, should I also store my company's employees as regular users, and just create a company entity for my company in the same way as other companies, thereby storing the data in the same fashion.

Ultimately, I could create logic that says if you are a user tied to company ABC (my company) then you get the admin rights to view all clients, edit etc. (permissions allowing), but if you are a user for company XYZ, you only get to view their data and edit (permissions allowing).

The only other way would be separating out the employees for my company to a different table. It just seems non-DRY to store the same format of data in 2+ locations.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Michael White
  • 1
  • 2
  • If there's no difference in the data columns between employees and other users, put them all in the same table. As you suggested, rows in the Permission table might have different values for employees and other users. – Gilbert Le Blanc Jul 25 '20 at 23:56
  • 1
    We can't decide what is "best". [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) This is a matter of design for DB/SQL subtyping/inheritance/polymorphism. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [How do you effectively model inheritance in a database?](https://stackoverflow.com/q/190296/3404097) Such design rearrangements are are not [tag:database-normalization]. Why do you think "normalization" is relevant? – philipxy Jul 25 '20 at 23:59

2 Answers2

0

You should have a table of users of your application. Period. That table can have a company and the company can -- by coincidence -- be your company.

You can enable logic, using a constraint or application logic, so that users of your company are considered "admin". I think I would be careful here. I'm not sure that all employees should have access to everyone's records. There may be cases where the access is more limited.

In other words, the users of the database should be based on the database needs. You seem distracted by the fact that they are employees of your company. That is extraneous. What you have are simply a set of users who happen to have admin access.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

You need to have one table to represent your users - otherwise your data will become denormalised. This table may hold company details, it may not - it's your choice.

You state that the rights will be based on the company that they work for. If you wish to tie access rights to the company, then you probably should have a company table - linked to the users table with a one to many relation.

You could either use the company table to define what access users from each company has (read, write, etc.) Or if you require more granular permissions - create a permissions table that links to the company table - so that users from company A, have admin access to applications 1 & 2 - but not 3.

Tim Wooldridge
  • 183
  • 1
  • 10