SQL Storage, Enums, Roles and Clean Coding !!!

[Mr Newbie]

Nope, I dont mean SQL Roles. I am writing an ASP.NET application using forms
authentication.

I have an enum which contains role enumerations :-

Public Enum Roles
User
Administrator
Accounts
Arbitrator
End Enum

I have an SQL Database which is to contain a field or fields which will have
the users roles stored within, A user may have one or more roles. In one of
the applications layers, I will use a function to do checks on the users
role such as

isUserInRole( User.Identity.Name , Roles.Administrator ) As Boolean

The challenge I face is how best to store the results so that I may cleanly
determine the roles which the user has. I dont like the idea of using
multiple columns because it's messy, but so is storing more than one role
type in a field, I would have do something like have comma seperated value
strings, so my users roles could contain values like 0,1,2. I would then
have to search this string or convert it to an array or something.

Is there a 'Clean' way to do this ?

Ideas anyone ?

 

[Mr Newbie]

OK, well in the absence of any feedback, I have decided to use both an Enum
and a Bit Coded integer stored in the Roles field this will enable me to
test the role by performing an AND on the Roles Enum values and the value
stored to see if using the Enum as a mask produces a True result.

This is the way Im going to go with this, but feel free to comment or add
new alternatives.

 

[Jason Kester]

This is Database normalization 101. You'll have 3 tables:

User
------
UserID
Username
etc...

Role
----
RoleID
RoleName

UserRole
----
UserID
RoleID


It's a simple many-to-many relationship. A user can be assigned to as
many roles as you'd like, and you can run trivial queries to discover
them. Comma delimited strings and Bit Fields are both Very Bad Things
in the context of relational databases. You should never use them
unless you know what you are doing and have a VERY good reason.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

 

[Patrick.O.Ige]

Mr NewBie..
You can got through an example for such scenario here at with regarding to
what Jason adviced:-
http://aspnet.4guysfromrolla.com/articles/082703-1.aspx
Hope that helps
Patrick

 

[Mr Newbie]

Yes, I know normalisation is an option. Its also very much slower. However,
at the end of the day it may be the correct solution from a purist point of
view.

As far as bit fields are comcerned, what do you see as being the danger in
using them. ?

 

[Mr Newbie]

Thanks very much for the article URL Ptrick, this does fit exactly with what
were are taling about and also the post by Jason. As I said to Jason, I was
aware of this approach but wanted a faster way of doing it, but this is
probably the way to go and is more flexible. It simply more troublesome and
slower to get the result.

--
Best Regards

The Inimitable Mr Newbie º¿º

 

[Mr Newbie]

I tried this article out and it works really well. I have modified my
database design and incorporated the ideas in the article and everyhing is a
lot cleaner now. I didnt realise that you could make use of roles like this
when not using integrated secruity.

Thanks ( ALL ) for your help - really good stuff

 

[Jason Kester]

Slower in what sense?

If you mean transaction speed, a normalized schema like the one I
supplied will return records much faster than what you are proposing.
Think of the processing involved in an operation like

select *
from User
where RoleID in
(select UserID from UserRole where RoleID=1)

This query is where the SQL Server team spends 90% of its optimization
effort. You're looking at roughly 0ms execution times for anything less
than a million records. Now look at what you are proposing:

select *
from User
where UserRoleString like '%Admin%'

There is simply no way to optimize this that does not require a full
table scan and a full text search of every single record. Basically
the slowest thing you can do in Transact-SQL, short of writing some
crazy cursor-based approach.

As to your second question, Bit Fields combine the performance
penalties seen above with the fact that adding possible Roles to your
system would require you to actually modify the table schema. Since it
would no longer be a 4 bit int, but rather a 5 bit int. Of course, you
could attempt to circumvent this by starting off with a large bit
field, say 32 bits for your int field, but then how would you ensure
relational integrity?

Anyway, you should always think long and hard about breaking database
normalization rules (at least the 3rd form). Those ideas are there to
ensure that bad data simply cannot fit into your database. Trust me,
within a year of implementing your comma-delimited role list, you will
find the values "ALL", "Don't Know", and "None" sitting in that column.
Even though there is no application capable of putting those values
there. I know this from experience. Bad data has a way of creeping in
behind your back!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

 

[Mr Newbie]

No your obviously mistaken I was not proposing a query like the one you
think.

An integer stored with bits that represent the role.

[0] User
[1] Administrator
[2] Accounts

. . .

This way I can mask the integer to see if someon is in the role. So this is
a single integer stored in the user table. I know that this is not quite
what the purist may like, but sometimes you dont need pursim. Now do you
understand ?

 

[Patrice]

Always measure. It's unlikely this solution will yield a visible performance
difference (keep in mind that going twice as fast is notn the same when you
goes from 30 s to 15 s or from 2 ms to 1 ms).

The bit mask solution sacrifies clarity and flexibility (in particular more
roles than what fits in your datatype ?) IMO for an unoticeable perf
improvment...

--

No your obviously mistaken I was not proposing a query like the one you
think.

An integer stored with bits that represent the role.

[0] User
[1] Administrator
[2] Accounts

. . .

This way I can mask the integer to see if someon is in the role. So this is
a single integer stored in the user table. I know that this is not quite
what the purist may like, but sometimes you dont need pursim. Now do you
understand ?

 

[Mr Newbie]

Your right, and thats why I opted for the solution you suggested in the
Rolla article. I was jsut trying to explain my thinking to the other guy.

I have to say, though in putting the right solution together, it was more
work. And I still say that its quicker performance wise ( probably ), but
you are correct in what you are saying without measurement, it is foolhardy
to make the statement so I accept this.

Thanks for your insight and help.

--
Best Regards

The Inimitable Mr Newbie º¿º

Always measure. It's unlikely this solution will yield a visible
performance
difference (keep in mind that going twice as fast is notn the same when
you
goes from 30 s to 15 s or from 2 ms to 1 ms).

The bit mask solution sacrifies clarity and flexibility (in particular
more
roles than what fits in your datatype ?) IMO for an unoticeable perf
improvment...

--

No your obviously mistaken I was not proposing a query like the one you
think.

An integer stored with bits that represent the role.

[0] User
[1] Administrator
[2] Accounts

. . .

This way I can mask the integer to see if someon is in the role. So this is
a single integer stored in the user table. I know that this is not quite
what the purist may like, but sometimes you dont need pursim. Now do you
understand ?

--
Best Regards

The Inimitable Mr Newbie º¿º

Slower in what sense?

If you mean transaction speed, a normalized schema like the one I
supplied will return records much faster than what you are proposing.
Think of the processing involved in an operation like

select *
from User
where RoleID in
(select UserID from UserRole where RoleID=1)

This query is where the SQL Server team spends 90% of its optimization
effort. You're looking at roughly 0ms execution times for anything less
than a million records. Now look at what you are proposing:

select *
from User
where UserRoleString like '%Admin%'

There is simply no way to optimize this that does not require a full
table scan and a full text search of every single record. Basically
the slowest thing you can do in Transact-SQL, short of writing some
crazy cursor-based approach.

As to your second question, Bit Fields combine the performance
penalties seen above with the fact that adding possible Roles to your
system would require you to actually modify the table schema. Since it
would no longer be a 4 bit int, but rather a 5 bit int. Of course, you
could attempt to circumvent this by starting off with a large bit
field, say 32 bits for your int field, but then how would you ensure
relational integrity?

Anyway, you should always think long and hard about breaking database
normalization rules (at least the 3rd form). Those ideas are there to
ensure that bad data simply cannot fit into your database. Trust me,
within a year of implementing your comma-delimited role list, you will
find the values "ALL", "Don't Know", and "None" sitting in that column.
Even though there is no application capable of putting those values
there. I know this from experience. Bad data has a way of creeping in
behind your back!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/