Sweetie Sweetie - 26 days ago 7
C# Question

Handling custom Roles based users in database

I am working on an application in which I am having 5-6 roles. Yes, It's a common thing but I am facing a issue. I am handling the data and tables of my own.

I have checked other posts but most of them is related to Membership.
Well, to handle this I have created a table that holds columns

TblUsers


  1. UserId

  2. Name

  3. Email

  4. Roles-- In this column I am storing roles like
    Teacher
    ,
    Student
    etc



But now I have come across the situation where a user can have
multiple roles. Suppose a user is a teacher and a student and also some more.

So, my question is that how should I manage my database so that It should work efficiently and a sensible database handling should reflect and in future also it should work well regarding fetching the roles and checking against the "Roles" and allowing the permissions.

One solution I thought is to keep the roles in different table

tblRoles

Role_Id Role
1 Client
2 Teacher
3 Student
4 Superitendent
5 Principal


and then keeping the roles in the users table as follows

tblUsers

User_Id Name Email Roles
1 Pin hh 1
2 Sin mm 1,2
3 ll ii 3,2
4 uu hv 3


or

User_Id Name Email Roles
1 Pin hh Teacher
2 Sin mm Client, Teacher.


But doing this will make the things complex in coding?

How will I join and get the roles if I use the above technique
Suppose user who has Id 2 has logged in and to get the roles of user I have to do like this?

var Roles=get Roles from tblUsers where user_id= 2


then

Roles.split(',');


then checking the count and getting the roles like this

var User_Role = get Role from tblRoles where Role='Roles[0]' ;


and If I have more than one values in Roles Array then I have to call Database again and again?

Please help me regarding this. Your suggestions would be really helpful to me.

Answer

You can make your tables like this: tblUsers:

User_Id      Name    Email
1            Pin     hh
2            Sin     mm
3            ll      ii
4            uu      hv

tblRoles:

Role_Id      Role
1            Client
2            Teacher
3            Student
4            Superitendent
5            Principal

tblUserRoles:

User_Id      Role_Id
1            1
1            2
1            5
3            4
3            2

For using this table you can simply using sql queries like this:

WITH Roles_CTE(User_Id, Role) AS(
SELECT User_Id.tblUserRoles, Role.tblRoles FORM tblUserRoles INNER JOIN tblRoles
ON tblUserRoles.Role_Id = tblRoles.Role_Id)

SELECT User_Id.tblUsers, Name.tblUsers, Role.Roles_CTE FROM
Roles_CTE INNER JOIN tblUsers ON tblUsers.User_Id = Roles_CTE.User_Id

And for using this query in c# you should use ADO.NET components. For sql-server users like you there is System.Data.SqlClient namespace.