What is the ideal design for creating master tables in database and using them in application

Question

I am designing a web application, as a first step I am creating a database. Now very basic table called Role which needs to be managed through database. This table will hold:

RoleID (Identity and Primary Key)
RoleName (Varchar)

Now in application if I need to check that for "Admin" role, enable one of the button else disable it. Then how should I write a code without hard coding "Admin" as Role?

What could be a good design in this case for storing Master like data in database and refer to it in application?

I used to do it with enum earlier but it seems its not much maintainable because if I add new Role in database then I also need to change the enum in code, e.g.

public enum Role
{
  Admin=1,
  Normal=2
}

See this thread: http://stackoverflow.com/questions/229856/ways-to-save-enums-in-database It seems to be a good idea to make the enum in code primary, and then merely push that information out to the DB at some point. (And read specific values back in) Note: your "Role" probably only means something after it is assigned to something, the table you describe would just hold a list of all possible values but wouldn't map roles to users or pages. — , Mar 06 '14 at 13:24
If this is an asp.net web application, why not use the standard forms authentication? — Aaron Palmer, Mar 06 '14 at 13:28
@AaronPalmer I don't recall ASP.NET forms auth providing a strong notion of groups (or roles) for one thing. Also, it tends to push you into storing authorization into XML files, when it's often better kept in the program's database where the program has an easier time managing it robustly. It may also push you into having "windows users" for each web user, which easily gets crazy. — , Mar 06 '14 at 13:33
@ebyrob, google asp.net forms authentication. It is very robust. You can use the out of the box role provider or write your own. Role management is actually one of its strengths. It provides a base set of SQL tables and does not require AD support. — Aaron Palmer, Mar 06 '14 at 13:57
@AaronPalmer I just know I tried it about 3 years ago for a website project and found several things about authorization management woefully inadequate. I believe it required at least one of: LDAP, XML, ActiveDirectory. It did not integrate well with our MySQL DB. It required us to hard-code role-names into pages we were writing. It did not provide simple names to the HR people assigning rights to users by checkbox per required "task". (Yes, you could call that a role, but... we were mapping tasks to pages through a UI driven table, not by coding the task "role" name into the page) — , Mar 06 '14 at 14:06

score 1 · Answer 1 · answered Mar 06 '14 at 13:15

1

The simplest solution would be to extend your table structure like this:

RoleID INT 
RoleName NVARCHAR
IsAdmin BIT

Or you could go with user1795177's approach of creating special permissions you assign to each role.

And please use NVARCHAR instead of VARCHAR in SQL Server to support unicode.

answered Mar 06 '14 at 13:15

Thorsten Dittmar

55,956
8
91
139

+1 for the NVARCHAR comment, I learnt my lesson the hard way with VARCHAR!! – Mo Patel Mar 06 '14 at 13:37
if 1 is the only admin, and 2 is the only normal than having IsAdmin, IsNormal, and IsHappyCamper flags all over doesn't seem that helpful. If the user can already have more than one role, that information is either already defined by RoleID or stored in the mapping to the user. Note: If you're advocating a single level setting for root/admin, then I'm not against that it can greatly simplify things... But it's usually outside the normal authorization configuration. – Mar 06 '14 at 13:55

score 0 · Answer 2 · answered Mar 06 '14 at 13:12

0

Creating a permission table would be better. But simple way is just to add a field '

PermissionType  int

and then you can assign permissions to different roles

answered Mar 06 '14 at 13:12

Bruce The Dragon

11
8

Might help us to know what permission means by examples. Are you thinking "insert/update/delete/viewmoney"? Where the permission is sort of a verb on the target being manipulated (perhaps page)? – Mar 06 '14 at 13:53

score 0 · Answer 3 · answered Mar 07 '14 at 19:26

You're button don't need to check for Role but instead Permission. ex: If you page allow to view/modify/add/delete items then you'll have 4 permission

public enum Permission
{
  View = 1
  Modify = 2
  Add = 4
  Delete = 5
}

In the database, you would have a Role table that indicate which permission they have.

Admin could have View/Modify/Add/Delete Normal could have view/modify

You then set the Add button state depending on the permission of the current role. That way you don't need to care how many and which role exists. To go a bit further, each Permission could be attached to an entity.

ex: Normal could have Add/Modify/View on the Clients entity but not the Invoice entity.

What is the ideal design for creating master tables in database and using them in application

3 Answers3