Much is in the air at the moment about digital tsunami, the Big Data movement and Business Intelligence technologies to name but a few. This post offers a concept that may reduce a tiny bit of the space along the way by showing how to store multiple choice answers in a single location.
One concept of storing option choices from forms, surveys and online exams for example is to store them combined as a single value. This removes the headache, sorry overhead, of dealing with dozens of boolean yes/no values that appear in a raw dataset and allows the Business Intelligence expert a more manageable view of what's on her plate.
I said any number of choices but this is actually limited to a database server's own limitations or the limits imposed by cloud based systems as part of their own governance rules.
Scenario:
Let's say we have a form on a web page that has 5 option boxes and a person completes the form and chooses the first, third and fifth choices.
Each option box should have assigned values 1 to 5 respectively and based on the choices made (1,3 and 5) we derive the single value to store as follows :
21 (option box 1) which is 2
+
23 (option box 3) which is 8
+
25 (option box 5) which is 32
So the single number to store that represents the choices 1,3 and 5 becomes 2 + 8 + 32 which is 42.
We of course need to unravel the number 42 to display the choices back onto the page or app (p.s...no hint here to the Meaning of Life!). To do this we start by taking the highest valued choice box and test if it's associated 25 value can be subtracted from 42 and if it can then we mark it's equivalent check box. The remainder is then used against the next lower value option box and repeated until we reach the lowest value box.
The steps below outline the full approach for this example.
1. Is 42 - 25 (25) greater or equal to 0?
42 - 25 = 10.
Yes it is so we subtract it and use the remainder in the next step.
We also mark the fifth choice option box as checked.
2. Is 10 - 24 (16) greater or equal to 0?
10 - 16 = -6
42 - 25 = 10.
Yes it is so we subtract it and use the remainder in the next step.
We also mark the fifth choice option box as checked.
2. Is 10 - 24 (16) greater or equal to 0?
10 - 16 = -6
No it's not so we leave it and don't check option box 4. Move to the next step.
3. Is 10 - 23 (8) greater or equal to 0?
10 - 8 = 2
3. Is 10 - 23 (8) greater or equal to 0?
10 - 8 = 2
Yes it is so we subtract it and use the remainder in the next step.
We also mark the third choice option box as checked.
We also mark the third choice option box as checked.
4. Is 2 - 22 (4) greater or equal to 0?
No it's not so we leave it and don't check option box 2.
2 - 4 = -2
5. Is 2 - 21 (2) greater or equal to 0?
2 - 2 = 0
Yes it is.
We also mark the first choice option box as checked.
This will now display option box 1,3 and 5 as checked.
Lastly, the single stored value will need reported on and for this you need to create a view which will be made up of a series of case statements one for each yes/no choice based on the logic above.
For those using SQL Server then a Common Table Expression (CTE) would provide a more efficient alternative.
We also mark the first choice option box as checked.
Lastly, the single stored value will need reported on and for this you need to create a view which will be made up of a series of case statements one for each yes/no choice based on the logic above.
For those using SQL Server then a Common Table Expression (CTE) would provide a more efficient alternative.
I have offered a concept here without any implementation code. This is because the problem can be solved using SQL, JavaScript, PHP, Ruby, C# or many other alternatives depending on the platform in use.
I hope this helps :)
Enjoy !
