January 15, 2003 at 1:37 pm
Oops forgot the controls that go onto the form. Add a checkbox, and 2 command buttons. Then set the properties for everything to the below settings. In particular the index on the checkbox must be set to 0.
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 870
ClientLeft = 60
ClientTop = 345
ClientWidth = 9360
LinkTopic = "Form1"
ScaleHeight = 870
ScaleWidth = 9360
Begin VB.CommandButton Command2
Caption = "Clear Setting"
Height = 315
Left = 4590
Top = 540
Width = 1695
End
Begin VB.CommandButton Command1
Caption = "Save To DB"
Height = 315
Left = 2895
Top = 540
Width = 1695
End
Begin VB.CheckBox Check1
Caption = "Question 1"
Height = 315
Index = 0
Left = 1628
Top = 150
Width = 6105
End
End
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 16, 2003 at 12:15 am
Hi all,
thanks for all opinions.
- simple ASP/VB-code rules out OR-ing and AND-ing
- simple SQL-code rules out bitmask
- since all data are loaded once, no updates, fast inserts not necessary
- number of advices can change so room for expansion must exist
- since number of advices is small (< 10.000) storage space no issue
- ease of select is an issue
- the data should be easy to look at in QA
so I am inclined to store the matrix in a bigint thus
2101011000000000000
Real bad?
/m
January 16, 2003 at 8:06 am
Though your question is about the best data type to use, in my mind there is still a big question about data integrity which Antares touched upon.
I see no problem in storing the answers to the questions in a single column but would suggest a table to maintain the integrity of the positions in this column. Perhaps something like this:
CREATE TABLE t_question (
question_pos TINYINT NOT NULL,
question_txt VARCHAR(255) NOT NULL, --size ?
active_flg BIT DEFAULT 1 NOT NULL,
...,
CONSTRAINT XPK_t_question PRIMARY KEY (question_pos))
I would then apply a trigger or security on this table to not allow a question to be changed once it is created (or answers exist for question_pos). If a question becomes inactive you just fill its position in the answer column with a 0.
Perhaps you are already doing something like this but I always get very nervous about data integrity since I have had to clean up to many messes left by others. Storing the answers in one column is quite like someone reusing an old column for something new.
January 16, 2003 at 8:17 am
quote:
- simple ASP/VB-code rules out OR-ing and AND-ing
The OR and AND in ASP / VBScript work EXACTLY the same as they do in VB. Try
it out.
quote:
- simple SQL-code rules out bitmask
The operators "&" and "|" are the equivalent operators in T-SQL. They work
exactly the same. Try it out.
quote:
- number of advices can change so room for expansion must exist
Run my second example for the VB app, you can change the number of questions all day as long the questions in one group never count more than 32.
But trying to store a concaneted string of 0's and 1's into number will only
bring you heart ache as the first time you have a leading zero it WILL
disappear, and ALL trailing zeroes WILL disappear.
EX:
quote:
2101011000000000000
WILL BECOME: 2101011 (How many answers did you have here?)
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Edited by - tcartwright on 01/16/2003 08:20:49 AM
Tim C //Will code for food
January 16, 2003 at 8:46 am
Hi tc,
I think you have a point
scenario
step 1: user fills in some data
step 2: user is presented 2 - 12 questions and answers them with y/n
step 3: program concatenates answers, ex: 4 question --> 1010 (or 1111)
step 4: program asks database for data sending in 1010
step 5: database concatenates 2 + 1010 + rightpadding with zeroes --> 2101000000000000000
step 6: database selects advices with key 2101000000000000000
step 7: user is presented with the advices
question raised
How do I see the difference between
a. user answered 4 questions --> 1010
b. user answered 5 questions --> 10100
??
January 16, 2003 at 8:58 am
I see what you're saying Tim. So:
Q1 = True
Q2 = False
Q3 = True
you store 5 in an int field. Binary 101 is 5 decimal. Later, if a 4th question is added, you store 5 if the answer is False (0101) and 13 if true (1101). Nice. Low space, very succint, no problems. The person pulling the data will have to know which bit belongs to which question though. Also, if you're pulling it using VB you're limited to about 32 questions.
January 16, 2003 at 9:45 am
Micheal did you get my zipped project with the example of binary comparisons? I can post it on my web site for download if you want me to.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 17, 2003 at 12:58 am
Hi tc,
yepp thank you, got it, tried it.
However I think yor solution has the same flaw as mine.
How do you differentiate between two questions with check and three questions with the first two check and the third unckecked? Both returns/stores as 3.
I'll think I use my solution 3:
3. Declare 2 bit columns, NOT NULL + 10 bit columns, NULL.
/m
January 17, 2003 at 7:34 am
They shouldn't be the same.
Each bit should be filled.
Questions 1 & 2 true, Question 3 false:
110 = 6
Questions 1 and 3 true, Question 2 false:
101 = 5
Questions 2 and 3 true, Question 1 false:
011 = 3
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 17, 2003 at 9:00 am
hi bkelley,
the trouble is knowing if a questions are asked AND the answer.
thereby
1 = yes answer
0 = no answer
null = null answer = no question asked
so
3 questions asked =
Questions 1 & 2 true, Question 3 false:
110 = 6
2 questions asked =
Questions 1 & 2 true:
110 = 6
/m
January 17, 2003 at 9:14 am
Why would you store
110 for 2 questions and not
11 = 3 for 2 questions.
And in the future what happens if you add or remove questions what do you plan to do?
January 17, 2003 at 9:18 am
If the possibility of the question not being asked or an answer not being given exists, you have at least 3 possibilities. Anything based on base 2 will thereby fail. The bit happens to work because of how SQL Server handles NULL. But at least when I was doing analysis of questionnaires distributed at several political conventions (college social science class), it was important for us also to capture the number of questions not asked and the number of questions not answered as separate entities. So that would make 4 possibilities. The bit field wouldn't work for that either.
The bitwise ops only work if you have just two options.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 20, 2003 at 11:41 am
Notes from the field:
We use bit-wise comparisons througout our entire database, not only for question and answer structures, but for all flags. It has worked well for us in a Web environment with 1.7 MM registered users. Flexibility is probably one of the primary reasons. You can add a question (or flag) and not have to change your schema. You get 32 flags per integer column and 64 with a bigint column.
And get this - we indexed our bitwise columns and it improved queries noticeably! (Only with direct comparisons, not with the view listed below.)
Bit wise operations are not very friendly to query tools and non-DBA types. So, we use views with case statements to make querying the bit wise columns "friendly". The end user never sees the bit wise stuff.
BTW why not have another bit wise column that determines whether or not a question has been answered? Here is some on the fly code that should work:
CREATE VIEW Yadda AS
SELECT
Question1 =
CASE
WHEN ISNULL(QuestionAnswered,0) & 1 = 0 THEN 'Not answered'
WHEN ISNULL(Answer,0) & 1 = 1 THEN 'Yes'
ELSE 'No'
END
, Question2 =
CASE
WHEN ISNULL(QuestionAnswered,0) & 2 = 0 THEN 'Not answered'
WHEN ISNULL(Answer,0) & 2 = 2 THEN 'Yes'
ELSE 'No'
END
, Question3 =
CASE
WHEN ISNULL(QuestionAnswered,0) & 4 = 0 THEN 'Not answered'
WHEN ISNULL(Answer,0) & 4 = 4 THEN 'Yes'
ELSE 'No'
END
, Question4 =
CASE
WHEN ISNULL(QuestionAnswered,0) & 8 = 0 THEN 'Not answered'
WHEN ISNULL(Answer,0) & 8 = 8 THEN 'Yes'
ELSE 'No'
END
FROM ....
IMHO the other approaches seem to be more complicated than necessary.
Bill Bertovich
bbertovich@interchangeusa.com
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply