Search code examples
database-designuser-roles

Convince a skeptic! Why do I need a "Roles" table in my database?

When designing the database tables for storing simple User/Role information, can anyone tell me why it would be a bad idea to store the Role information directly on the User table (for example, Comma-Separated in a Roles column).

Thoughts:

  1. The database doesn't need to know about the roles, that's the UI's domain
  2. The quicker the access to a specific user's roles the better
  3. Sure, if sometime in the future I want access to all of the users for a specific role the query might

be a little slow, but who cares at that point?

Does this make any sense? Am I off my rocker? Wouldn't creating Roles and UserRole tables be overkill and add unnecessary sql and code overhead?

UPDATE:

To further illustrate my point... in code, I want to know if user "Steve" is in role "Administrator".

Option 1: query the UserRole table for a list of roles for user "Steve". Loop through that list and see if the RoleName matches "Administrator".

Option 2: split the csv in the User's Role property and see if the resultant list contains "Administrator"

UPDATE II:

I agree that my suggestion violates all sorts of "best practice" type thinking, particularly around DB design. However, I am not seeing how the "best practices" make any sense in this sort of scenario. I do like to rock the best practices boat now and then...I like to code in a way that seems smart, which means sometimes I need to understand more to know when I'm not being smart :)

Solution

  • Because it violates 3rd normal form. You want to seperate all of your entities as different objects which means you need a seperate table, as well as a relationship table.

    Violation of data

    If you keep all of this in one table you are placing too much irrelevant information about a user in a user's table. A user's table should have fields that pertain only to that user, such as their name, user account, etc. But in this case you've decided to throw in some role information. This doesn't make sense as you are adding attributes that do not necessairly have anything to do with a user.

    The result is you start adding fields that do not pertain to a user, and you end up having a ton of unrelated information. The solution to this is have a users table like so:

    User table
UserID
User
...

Role table
RoleID
Role
....

User Role Table (the relationship)
UserRoles
UserID
RoleID
...

    Updating / Inserting of data

    The next issue you have to deal with if you store role information inside of a user's table is how to perform valid updates, inserts. This makes it all the more difficult. When you edit a record, you have to ensure you edit the right value from the CSV.

    Finding the right role

    Here lies the most difficult problem, how to find the role given a user. You might come up with this great parsing technique in C# or SQL Server, it works great..but it becomes terribly slow and hard to read. You start dealing with SubString(), Left(), Right(), Len() and a whole slew of other functions just to parse out the role of a user.

    The Solution

    You may think that putting it all into one table is easier right now. It probably will take a lot less up front time. But you have to develop applications with the future in mind. The UI will be much simplier if you follow the rules of 3nf and create a nice relational structure. Not only will the UI admin screens look nice, but getting a role for a specific UserID will be so trivial, as opposed to parsing or searching...