|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Storage, Enums, Roles and Clean Coding !!!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 ? -- Best Regards The Inimitable 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. -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Mr Newbie" <h***@now.com> wrote in message news:eWo3MSh7FHA.4076@tk2msftngp13.phx.gbl... > 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 ? > > > -- > Best Regards > > The Inimitable Mr Newbie º¿º > 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/ 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. ? -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Jason Kester" <jasonkes***@gmail.com> wrote in message news:1132533691.076675.137480@g14g2000cwa.googlegroups.com... > 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/ > 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/ --- Get your own Travel Blog, with itinerary maps and photos! http://www.blogabond.com/ 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 ? -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Jason Kester" <jasonkes***@gmail.com> wrote in message news:1132625278.847945.241790@g49g2000cwa.googlegroups.com... > 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/ > > --- > Get your own Travel Blog, with itinerary maps and photos! > http://www.blogabond.com/ > 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... -- Show quote"Mr Newbie" <h***@now.com> a écrit dans le message de news:OS1Kv607FHA.636@TK2MSFTNGP10.phx.gbl... > 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 º¿º > > "Jason Kester" <jasonkes***@gmail.com> wrote in message > news:1132625278.847945.241790@g49g2000cwa.googlegroups.com... > > 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/ > > > > --- > > Get your own Travel Blog, with itinerary maps and photos! > > http://www.blogabond.com/ > > > > 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. -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Patrice" <nob***@nowhere.com> wrote in message news:u%23yP4S17FHA.2616@TK2MSFTNGP15.phx.gbl... > 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... > > -- > > "Mr Newbie" <h***@now.com> a écrit dans le message de > news:OS1Kv607FHA.636@TK2MSFTNGP10.phx.gbl... >> 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 º¿º >> >> "Jason Kester" <jasonkes***@gmail.com> wrote in message >> news:1132625278.847945.241790@g49g2000cwa.googlegroups.com... >> > 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/ >> > >> > --- >> > Get your own Travel Blog, with itinerary maps and photos! >> > http://www.blogabond.com/ >> > >> >> > > 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 Show quote "Mr Newbie" <h***@now.com> wrote in message news:O4KzRmi7FHA.3592@TK2MSFTNGP12.phx.gbl... > 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. > > -- > Best Regards > > The Inimitable Mr Newbie º¿º > > > "Mr Newbie" <h***@now.com> wrote in message > news:eWo3MSh7FHA.4076@tk2msftngp13.phx.gbl... > > 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 ? > > > > > > -- > > Best Regards > > > > The Inimitable 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. -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Patrick.O.Ige" <naijaco***@hotmail.com> wrote in message news:%23uis0jl7FHA.3636@TK2MSFTNGP09.phx.gbl... > 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" <h***@now.com> wrote in message > news:O4KzRmi7FHA.3592@TK2MSFTNGP12.phx.gbl... >> 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. >> >> -- >> Best Regards >> >> The Inimitable Mr Newbie º¿º >> >> >> "Mr Newbie" <h***@now.com> wrote in message >> news:eWo3MSh7FHA.4076@tk2msftngp13.phx.gbl... >> > 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 ? >> > >> > >> > -- >> > Best Regards >> > >> > The Inimitable 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 -- Show quoteBest Regards The Inimitable Mr Newbie º¿º "Mr Newbie" <h***@now.com> wrote in message news:uIyWXNn7FHA.4036@TK2MSFTNGP11.phx.gbl... > 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 º¿º > > > > > "Patrick.O.Ige" <naijaco***@hotmail.com> wrote in message > news:%23uis0jl7FHA.3636@TK2MSFTNGP09.phx.gbl... >> 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" <h***@now.com> wrote in message >> news:O4KzRmi7FHA.3592@TK2MSFTNGP12.phx.gbl... >>> 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. >>> >>> -- >>> Best Regards >>> >>> The Inimitable Mr Newbie º¿º >>> >>> >>> "Mr Newbie" <h***@now.com> wrote in message >>> news:eWo3MSh7FHA.4076@tk2msftngp13.phx.gbl... >>> > 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 ? >>> > >>> > >>> > -- >>> > Best Regards >>> > >>> > The Inimitable Mr Newbie º¿º >>> > >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics