-3

I have a specific application where users could have different roles. Apart from different permissions controlled by the business logic, certain roles of users need additional linked info. For example, the Requestors are linked to departments.

So the question is which is the better way to go aa far as the design goes: 1 User table with department_id linked to the Department table that will be null for non requestors. Role table with user roles UserRole linking table

2 User table Requestor table with department_id linked to departments and user_id linked to users Role table with user roles UserRole table

So, in other words, for the special types of roles that might need to have or be linked to additional data, do I create separate tables, or bunch all together inside the user table and handle the rest via the roles and the application logic?

Dharman
  • 30,962
  • 25
  • 85
  • 135
artybug
  • 30
  • 2
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Aug 24 '18 at 09:44
  • Hi. This is sql/database subtyping/inheritance. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. – philipxy Aug 24 '18 at 09:46
  • I consider this a distinct example that, given the specific context, may not be practically addressed by the purist design patterns. – artybug Aug 24 '18 at 11:36
  • I don't know what "purist" means but if you write/code your design alternatives clearly & in detail you will find them addressed there & in many other such Q & As. – philipxy Aug 24 '18 at 17:57
  • If they were addressed, I wouldn't be posting here. – artybug Aug 24 '18 at 18:23

1 Answers1

0

Hope all the user can't be requester. Also If you link dept and user then dept_id will be FK in user table that wont be good. Also in future if you need to link some other info then you have add another attribute in user table. Bridge table between user and department will be good same way like userRole bridge table. If the user is requester then bridge table will have record for that user (user_id and dept_id). In future if you want to create different link then you can create another bridge table and maintain instead of adding attribute in user table

Gaj
  • 888
  • 5
  • 5