what is a good use case for splitting user table by role

Question

I have a requirement where there are primarily 5 roles in the system. Each of these roles increase in hierarchy from junior to senior most role. Each junior role is a subset of its immediate next senior role in terms of access privileges. The roles and their hierarchy in increasing order would be J1-J2-S1-S2-SuperAdmin.

Each role needs to store additional information about the user that is only specific to that role for eg. J1 needs to store grade and location, whereas J2 will only need to store grade. S1 and S2 that manages J1 &J2 needs to store neither.

In a nutshell, its all about separating the admin role users from the non admin role users. The admin roles have a different set of fields which need to be mandatory and the general users who are not admin need to have a separate set of fields that need to be mandatory. For eg. First name, last name are mandatory for all users. Whereas location is only mandatory for non admin users. IF I keep all users in one table, then I cant enforce allowing nulls in certain fields for a certain role while disallowing it for other roles. Also due to admin users being far fewer than non admin users, the data access for admin users at login time could be faster for users belonging to admin roles? There is also the possibility of feilds being added or removed changes being made as which fields are mandatory or non mandatory for non admin users.

I am seeking opinions if anyone has encountered such a situation before and if anyone recommends splitting up tables by roles for such reasons. In such a scenario as mentioned above, what are the arguments that favor storing each role's users in a separate user table? Wont I need to scan each table and find which table the user is in before I authenticate the user? Would there be any specific problem if all such users are stored in the same table? What are the pros and cons of either approach? if I seem to be favoring such a design as mentioned above its only because I'm playing the devil's advocate.

Answer 1

What you are describing is an example of the general situation entity sub-typing.

There are two basic ways to handle entity sub-typing:

One method places common attributes in a table and distinct attributes in separate tables, linked using 1:1 relationships to the common table. Some other considerations:

• The common table may have a partitioning attribute, which is a field that indicates which sub-type of thing each record is.
• The primary key of each sub-type table is a foreign key to the common table.
• You can create views which merge the columns from the common table and each sub-type table, so that for querying purposes you appear to have a "one stop shop" for each sub-type of thing. This can simplify your queries since it leaves you one less join to write out each time.
• This approach makes it easy to apply referential integrity if you have child tables that apply to only a single sub-type (or all sub-types).

Another method is to place all attributes in a single table, and then use declarative constraints and/or triggers and/or application logic to enforce sub-type specific business rules. In this case:

• A partitioning attribute must be used to indicate which sub-type of thing each record is.
• This makes queries easier, but it spreads your business logic out in a way that many people will find confusing and undesirable.
• This approach can lead to many nullable columns, which makes some people itchy.
• This approach doesn't help you if you have child tables that apply to individual sub-types, but it is the only sane approach if you have child tables that apply to multiple, but not all sub-types.

In your case you could pick either approach. Which might be better depends on your preferences and on exactly how many distinct columns there are per sub-type.

Answer 2

I would have one USERS table to hold all users with a foreign key (RoleId) to a ROLES table. A non-clustered index on `RoleId will help with the performance of looking up admin users.

For things like grades and location, where it's optional for some roles and mandatory for others, you have two options depending on the business rules:

• Make it nullable as you said.
• Create a table for each attribute (e.g. GRADES, LOCATION). Each will have the UserId as the primary key, and the value (e.g. grade, location) as a column. The value could be a foreign key to another table for more denormalizing (e.g. LocationId is a foreign key to LOCATION).

For new fields, create a new table. For removing fields, you can simply deprecate or remove the table.

Use stored procedures to facilitate CRUD actions for users of different roles. Keep the stored procedures simple if you want maintainability and readability; you don't want complex business rules in stored procedures.