Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31346 > unrolled thread

Storage of "Do Not Show This Again" Flags

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  Storage of "Do Not Show This Again" Flags Michael Cole <invalid@invalid.com> - 2019-02-05 12:49 +1100
    Re: Storage of "Do Not Show This Again" Flags Erland Sommarskog <esquel@sommarskog.se> - 2019-02-05 23:48 +0100

#31346 — Storage of "Do Not Show This Again" Flags

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?

-- 
Michael Cole

[toc] | [next] | [standalone]

#31347

Michael Cole (invalid@invalid.com) writes:
> 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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web