Storage of "Do Not Show This Again" Flags

[Michael Cole]

More of a generic theortical question rather than coding...

For our application, we now want to introduce the concept of "Do Not
Show This Again" tickboxes on popup dialogs. I need to store and
retrieve these from the database. I am looking for suggestions on how
they should be stored.

The values will need to be stored against a user, for which we have a
foreign key. I will provide stored procs for accessing these values -
read and update. Options I had were: -
1. Single table, dual PK of Flag ID and User ID, with a single bit
field to hold the value - non-existance will be considered as false. We
could also have a string field to provide a description of what the
flag is. Or perhaps better would be a linked table for the flag
definition.
2. Single table, PKID of User ID, with a lot of bit fields for the
values - I hold a manual spreadsheet of what column is what flag, and
simply tell the developers which column to use when they request a new
flag
3. Single table, PKID of User ID, with a bit-masked long integer for
values - I hold a manual spreadsheet of what column is what bitmask,
and simply tell the developers which number to pass when they request a
new flag

Obviously, the number of flags is uncertain, and will increment as the
developers request new flags for new functionality, but the table
itself does not need to be that clear in its operation, and it is
purely a behind-the-scenes operation.

Has anyone done this before, and what basic structure did you use?

[Erland Sommarskog]

Post by Michael Cole
For our application, we now want to introduce the concept of "Do Not
Show This Again" tickboxes on popup dialogs. I need to store and
retrieve these from the database. I am looking for suggestions on how
they should be stored.
The values will need to be stored against a user, for which we have a
foreign key. I will provide stored procs for accessing these values -
read and update. Options I had were: -
1. Single table, dual PK of Flag ID and User ID, with a single bit
field to hold the value - non-existance will be considered as false. We
could also have a string field to provide a description of what the
flag is. Or perhaps better would be a linked table for the flag
definition.

I have not implemented something like this(*), but this appears as the
obvious design to me. It is quite clear that new tickboxes will be
added as your app evolves. Having a table with many bit columns,
means a lot of work when new flags are added.

I would make the table a two-column table: UserID and Flag. Unless
there are som nuances that require more than two values, I see no
reason for a bit column. Flag present => Flag set. Flpag absent => not
set.

I would also have a lookup table for the flag definition, with an FK
constraint to this table. This traps mispelled flag names. (I would
prefer to use codes rather than numeric ids.)

(*) OK, so I have made a table design exactly like this, but it
nothing to do about checkboxes, but a lot of binary properties
on accounts.