storing check box state in database with bitwise operation?

Question

let say you have between 35 and 50 checkbox on a form.

would you create 2 tables:

accessTable, field: userid, accessType
accessLookup, field: accessType, description

and add X and/or remove rows to accessTable depending on the user selection

OR

would you create 2 tables:

accessTable, field: userid, haveAccessBit
accessLookup, field: accessBit, description

and sum up the selected check box into haveAccessBit field?

* Edit *

I have to make my choice in the next 2 hours. For now, I would decide with a Heads or Tails.

Answer 1

There's no way I would use a bitfield for this. It would be too annoying to make sense of the data when looking at it directly.

For only 35 to 50 checkboxes, I would just add columns for each checkbox.

Which is easier to maintain? Your first choice might be easier to maintain on the database side, but might require more work on the coding side. Separate columns might require more DB maintenance (especially if you add new columns), but might be easier to manage on the coding side.

Answer 2

@ Paul lefebvre

it's not difficult

a simple query like

 SELECT     accessTable.User_ID, accessTable.Username, accessLookup.accessDescription
 FROM         accessTable INNER JOIN
                  accessLookupON 
        accessTable.haveAccessBit & accessLookup.accessBit = accessLookup.accessBit
 ORDER BY accessTable.User_ID, accessLookup.accessBit

would show up like

 userid, username, bitdescription1
 userid, username, bitdescription4
 userid, username, bitdescription8
 userid, username, bitdescription14
 etc...

Answer 3

I would probably go with your first option. It is flexible, scalable and pretty simple to implement. Also, it will be clear to anyone unfamiliar with your code as to what is going on.