February 4, 2019 at 6:29 pm
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.
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?
February 4, 2019 at 7:15 pm
bitwise can be done - just on SQL Server you have a straight use of it on @options https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-2017
or Active Directory on user/group account control field http://www.selfadsi.org/ads-attributes/user-userAccountControl.htm
the limit of how many flags you need is what you need to consider - if you going to need 1000 flags this may not be the best option.
And do not use a spreadsheet alone - use a table to hold those values as you can then easily report on the flags internally should you need it.
February 5, 2019 at 8:52 am
Options 2 and 3 both violate First Normal Form. It would be a major pain to add new flags, and there wouldn't be an easy way to discontinue flags.
Option 1 is mostly good. You do not need a bit flag. Mere existence of the pair indicates true, and absence of the pair indicates false. Also, a description should be in a separate flag table, because it's an attribute of just the flag, and not the user/flag pair.
Another version of option one would be to add a from date and to date if you want to track historical preferences. In that case, true would be indicated by a record with an "unknown" to date, and false would be the absence of a record with an "unknown" to date. I would recommend that an "unknown" to date be represented with a far future date such as 9999-12-30 or 9000-01-01 instead a NULL value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply