Using Bitwise Operators and Bitmask on very large enumerated lists

I have various functions in VBscript which take numeric values from an SQL database and perform bitmasking to determine matches. Better to show than try and explain:

The database may have a table with the following structure (example that doesn't actually exist)

Table: Group
Fields: ID, Description

The data contains:
ID Desc
1  Admin
2  Support
4  Marketing
8  Accounts
16 HR
32 Sales


I use bitwise operands to determine who has access to a part of the web application. So if the part of the application is marked with permissions of 37, the bitwise calculation determines that Sales(32), Marketing(4) and Admin(1) have access (32+4+1).

I use this method for a number of different things and have happily used this for a number of years.

I now have an issue in that one area were enumeration is used will potentially have hundreds of options. This poses a couple of problems.The numbers start to get so huge that the DB won't be able to store them as numeric types (SQL's BIGINT allowing storage of 64 items using my calculations). Secondly, VBScript itself will have problems trying to do a bitwise calculation on a number which is so large - it will be too large to be convertible to any VB number types.

I'm sure someone must have come up with a solution but I've searched around and found glimpses of ideas. I think it may be possible to store the data in the DB as a binary varchar thus allowing me to store the actual number. I would then have the problem of converting that number to something that VBScript can operate on to determine the result.

I also use SQL Reporting Services so any solution for VBScript would need to be useable in that too.

All help is appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

With the advent of gigabyte drives and RAM, bitwise operations seem out of place. What are you saving space-wise? Or is it just a "cool factor" of doing something clever?

It might be time to rethink your methodology, and go with a 0/1, true/false method instead. It is simple and scales well, unlike your current method (which is neither).
mccabrAuthor Commented:

Using the above for a number of years has made things simpler for me. The example I provided is only one area where this is used. There are other areas were the database has a list of options which grows and shrinks. This mechanism allows me to let users manage groups, permissions and options without any recoding of the database or application code. Quite how testing for True/False (0/1) would work I have no idea.

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mccabrAuthor Commented:

I will have a look at those links, I had found them already but wasn't registered with the site, I now am.
If it takes one byte to store 8 values, and you need to store hundreds, you will be using more storage for your flags than for the data you are cataloging.

1 byte = 8 flags
10 bytes = 80 flags
50 bytes = 400 flags
100 bytes = 800 flags

You could give each page of the web app a minimum security level - say 1 through 8 - and then set multiple one-byte flags per user per page. That would keep the flag space to a minimum, but it would still increase the storage requirememnts.

You could then abandon the bitwise hazzagazza in favor of a more orderly - and less computation-intensive method. Like, User A has security level 9, and only those pages with that level (or lower) are available.

Without knowing more of what is involved, it is hard to be more specific. I'm thinking bitwise operations will have to go.
mccabrAuthor Commented:
I had to move away from access levels as described above as the permissions are not linear. There is also the need to add new levels. I use this to store multiple options which are added to regularly but we can keep this to the permissions analogy. There is a requirement to have user A with level 9 access AND user B with level 1 access both accessing the same part of the application, as I said it can't be done with a linear approach.

I know that using bitmasks makes this more complicated and add a computational complexity but I am happy with that given it provides a more flexible system rather than creating columns for new bit fields. As it stands I do my maintenance via web pages and don't have to modify the SQL structure.

If we can move on from the rights or wrongs of using bitmasks I still have the need to store bitmask values into the 100s on a single field. This will probably involve SHIFTING bits in calculations and this is the part I'm not sure how to do.
mccabrAuthor Commented:
In my post above i should also have said that User A can access some items user B can't and vice versa.
mccabrAuthor Commented:
Ok, I dont' think I have found a reasonable solution so I will accept that using a database this way is wrong. I now wish to approach this issue with the following DB structure

ID: unique_id
NAME: string

ID: unique_id
DESC: string

ID: unique_id
USER_ID: ID from user table
ACCESS_ID: ID from access table

Now I know how this works and the DB is normalised, my issue with this is in marrying the db structure with simplified generic ASP forms.

If I wish to update user A I want to present the following HTML form:

INPUT: NAME ________
CHECKBOXES: ACCESS DESC X (with one checkbox per access in the access_table)

This may present 50 possible checkboxes, I understand how these are posted and am happy parsing the posted variable etc., My issue is in building SQL to update after form submission. On a single table this is fine, I can update user record but I would have to write up to 50 statements for the checkboxes (I would need to test if a user/access record existed and create new or update the existing record with it's checked value?)

This is the bit I am struggling with, I wish to use the solution for a LOT more than just user access, I have many areas using multivalued attributes at the moment and was using bitwise fields for these, now the bitwise fields can't handle the volume of options I need to figure out the best means of implementing the above?

>>I would have to write up to 50 statements for the checkboxes (I would need to test if a user/access record existed and create new or update the existing record with it's checked value?)

Yes, unless you chose to use a string of 50 (or so) "0" and "1" to indicate which options are available per user:

"10010100101...100" in a single record per user instead of the grid table.

You could declare the string length as, say, 255, which would allow for expansion.
mccabrAuthor Commented:
Is that not the same issue with using bitwise (minus the field size limits) in that the field is still holding multivalued attributes? All the stuff I've been reading argues against this for retrieving/reporting etc.,

I understand that this would solve my problem but it would use a similar mechanism to do so. I would be happy doing this but I don't want to go against the grain of SQL devs.
Normalization only goes so far, then the real world interferes.

If you would rather issue 50 (or more) update commands agains the database, I'm sure 50% of folks will say that is the way to go. But I'll bet there are 50% of folks that would say, "Who cares, as long as the solution works."

I'm in the latter 50%.

Ultimately, you have to determine which method to use. There is no one here who has your insight into the situation. As long as you document how your mechanism works, I don't think anyone can find fault. If they do, tell 'em to buzz off - it's a small world, perhaps, but it's your world, after all ;-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mccabrAuthor Commented:
I have to say thank you Badotz, I haven't had any people discuss the merits of normalization v processing/code savings and suggest that in some circumstances normalization loses.

I happen to agree with you on this, if there were an easy way to do the updates I would do that but you have given me the confidence to carry on with your suggestion.
Grade "B"? Was I bad?
mccabrAuthor Commented:
Not at all A = Excellent, B = Good, C = Average.

I only gave B because I had considered the solution myself. If I thought a B was a negative rating I would certainly not have chosen that.
mccabrAuthor Commented:
Have considered this again and think you are right, regardless of whether I considered the solution personally, your suggestion was excellent in that it provided a workable solution and provided me with the courage to go with my convictions. I tried to amend the above rating to A but was unable?
Click the "Request Attention" link on your original post. Ask a moderator to re-open the question so you can change the grade.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.