miral13
asked on
Bit mask advantages/disadvantages
Hi guys,
just wondering if you could give me your input on the usage of a bit-mask field for storing user access rights, rather than having a separate table. Yes I know it is not normalized. Input on indexing is also appreciated.
Database: SQL Server
Thanks in advance
just wondering if you could give me your input on the usage of a bit-mask field for storing user access rights, rather than having a separate table. Yes I know it is not normalized. Input on indexing is also appreciated.
Database: SQL Server
Thanks in advance
chapmandew
Not exactly sure what you mean...can you give an example?
ASKER
Let's say I have the rights stored in a table
1 - Home
2 - Products
4 - Downloads
8 - Forum
And I have users in another table, with their access rights
Michael - 15 (access to everything)
Laura - 3 (only Home and Products)
We can use the bitwise operators to query this.
What would be the disadvantages of this solution rather than having a table with the following associations?
Michael - Home
Michael - Products
Michael - Downloads
Michael - Forum
Laura - Home
Laura - Products
1 - Home
2 - Products
4 - Downloads
8 - Forum
And I have users in another table, with their access rights
Michael - 15 (access to everything)
Laura - 3 (only Home and Products)
We can use the bitwise operators to query this.
What would be the disadvantages of this solution rather than having a table with the following associations?
Michael - Home
Michael - Products
Michael - Downloads
Michael - Forum
Laura - Home
Laura - Products
OK...I gotcha now. To be honest, I've always kinda liked this idea. The only tricky thing is if you ever need to do any joins basped on that data...it makes it more difficult to look up. however, if you don't have that many values, then it might work out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>just wondering if you could give me your input on the usage of a bit-mask field for storing user access rights<<
We use this approach extensively. We have never had to do this, but if you do notice some slowness you can create computed columns on the bit column to represent the all the options:
1 - Home
2 - Products
4 - Downloads
8 - Forum
You can then index those columns.
We use this approach extensively. We have never had to do this, but if you do notice some slowness you can create computed columns on the bit column to represent the all the options:
1 - Home
2 - Products
4 - Downloads
8 - Forum
You can then index those columns.
Slightly off-topic, but it is mind blowing what a hard time I have had explaining this subject at work: People just don't get it. To the point that I have had to write functions just for extracting the values into "readable" format.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I already had this article http://sqlfool.com/2009/02/bitwise-operations/ which covers the sujbect much better. But I am looking for input from people who actually used this and found any disadvantages and how they managed the indexing.
It looks like that has been covered. What else were you looking for?
ASKER
As I said. Indexing and disadvantages from other users' experience
I suspect that if you read the comments posted, both Tim, Brandon and I covered that and possible workarounds.
ASKER
I am looking for new information. Something based on real experience with bit mask fields. I keep empathising on the keyword disadvantages. I want to know if somebody has implemented and what was the impact on performance. I am sorry but I don't think that this question received all the possible answers yet. Have a nice weekend.
Actually, it has....you're received answers for advantages and disadvantages.
>>Something based on real experience with bit mask fields<<
Yes we gave that to you.
>> I keep empathising on the keyword disadvantages.<<
And that was covered when Tim discussed Joins and Brandon focused on searching.
>>I want to know if somebody has implemented and what was the impact on performance.<<
We have all told you we have. I added that in our case the impact was minimal and suggested workarounds if this was not your case.
Yes we gave that to you.
>> I keep empathising on the keyword disadvantages.<<
And that was covered when Tim discussed Joins and Brandon focused on searching.
>>I want to know if somebody has implemented and what was the impact on performance.<<
We have all told you we have. I added that in our case the impact was minimal and suggested workarounds if this was not your case.
Normally I dispute poor grades. Normally those poor grades are Bs. You got good information from 3 TOP experts on EE and you dismiss is because you are unwilling, or unable, to comprehend the answers provided.
ASKER
Sorry Brandon because I upset you. Please check this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59778
In the end it provided more info than the replies posted here. And yet I closed the question.
You can choose to be upset or you can understand that there was more feedback that could have been provided.
In the end it provided more info than the replies posted here. And yet I closed the question.
You can choose to be upset or you can understand that there was more feedback that could have been provided.
You asked for "OUR EXPERIENCES". We gave you our experiences. Would you have preferred that we made something up?