January 14, 2003 at 10:15 am
Hi,
if I want to save an array of 0 (zeroes) and 1 (ones) with an upper bound
varying between 2 to 12 in a table, what are best:
1. Declare one column, varchar (12), string the 0/1 together and INSERT.
2. Declare one column, bigint, string the the 0/1 together and adding
another digit in front in order to not loose some zeroes, convert from char and INSERT.
3. Declare six bit columns, NOT NULL + six bit columns, NULL. The SELECT gets tough in this case.
4. Have a mother-child table construct (this I do not believe in)
In case 1 and 2, how to create a check constraint guarantiing it is only one
or zero in each position?
Other thoughts?
/m
January 14, 2003 at 10:23 am
Hmmm, if using a numeric data type you will lose all leading zeroes, so that
rules out #2. #3 is not bad, you can use a view or an UDF to represent the
columns as one, and join that back into your main selection. #4 for this
scenario I don't think this is for you either, but are you speaking in general?
For #1 the only way that I can think of to check constraint this would be to use
a UDF to loop thru all the chars in the inserted string and verify they are
boolean. Are you storing preferences or something similar? Because even though I
have given you advice on how to do what you want to do, IMHO I have concerns over the
extensibility of this design if that is what you are doing....
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 14, 2003 at 10:26 am
My Suggestion is this: If storing the state of settings of some kind, use bit wise comparisons, and store the number as a sql int (VB long).
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 14, 2003 at 12:06 pm
quote:
Are you storing preferences or something similar?
I am storing yes/no answers to a variable numer of questions. The concatenated set of 1/0 should give an answer stored in a table.
/m
January 14, 2003 at 12:33 pm
K, then I still suggest using bitwise operations as that is your cleanest solution. Then from VB, C++, etc. you can use the bitwise "OR" and "AND" to assemble them and compare if the answer is in the result. I can post a small simple example of using the bitwise operators in VB to do exactly this if you so wish.
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 15, 2003 at 3:00 am
I havent been very clear I realize on my question.
I have to store advices based on answers to questions.
A person can get from two up to twelve questions.
The number of questions depends on some background data about the person.
(all this are in some very normalized tables).
The questions can be answered with Yes or No.
Depending on the persons answers on this questions and the persons marital status, existance of kids, riskdispositon etc etc he/she should get some advices.
One or many.
These are not saved in the database, just presented on the screen.
The questions can be answered with Yes or No.
For this "relation table" I would need some fixed number of columns and some variable number of columns.
Variable number of columns isnt possible.
The number of questions can be changed tomorrow to be more than 12.
The answers are only selected (inserted once).
Data retrieval is by a stored procedure.
This solution
CREATE TABLE Advices
(MaritalStatus INTEGER NOT NULL CHECK(IN (1,2,3),
HasKids INTEGER NOT NULL CHECK (IN 0,1),
Response1 CHAR(1) NOT NULL DEFAULT 'n'
CHECK(response IN ('y','n')
...up to 6
Response7 CHAR(1) NOT NULL DEFAULT 'n'
CHECK(response IN ('y','n','-')
..up to 12
AnswerNumber INTEGER FOREIGN KEY references other table )
is theoretically correct (?).
Adding some columns is not a big deal when the number of question changes.
This solution
CREATE TABLE Advices
(MaritalStatus INTEGER NOT NULL CHECK(IN (1,2,3),
HasKids INTEGER NOT NULL CHECK (IN 0,1),
Array CHAR(12) NOT NULL,
AnswerNumber INTEGER FOREIGN KEY references other table )
)
is theoretically un-sound (?).
Changing Array(12) to Array(14) is not a big deal when the number of question changes.
The check constraint is here a problem.
This solution
CREATE TABLE Advices
(MaritalStatus INTEGER NOT NULL CHECK(IN (1,2,3),
HasKids INTEGER NOT NULL CHECK (IN 0,1),
Array BIGINT NOT NULL,
AnswerNumber INTEGER FOREIGN KEY references other table )
)
is theoretically un-sound (?).
When the number of question changes no change of DDL necessary.
The check constraint is here also a problem.
Which to prefer?
January 15, 2003 at 5:08 am
This is the table setup we use. It allows for adding questions to questionaries and knowing which questionaire a user took. In addition it stores all their answers. We also use this to allow customers to build their own questionaires and our ASP can buil a report dynamically from the questionqire to quesion mapping.
CREATE TABLE [SubmittedBy] (
[SubID] [int] IDENTITY (1, 1) NOT NULL ,
[LName] [varchar] (50) NOT NULL ,
[FName] [varchar] (50) NOT NULL ,
[QAID] [int] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[SubID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Questions] (
[QuestID] [int] IDENTITY (1, 1) NOT NULL ,
[Question] [varchar] (255) NOT NULL ,
PRIMARY KEY CLUSTERED
(
[QuestID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Questionarie] (
[QAID] [int] IDENTITY (1, 1) NOT NULL ,
[QATitle] [varchar] (50) NOT NULL ,
PRIMARY KEY CLUSTERED
(
[QAID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [QAtoQuests] (
[QAID] [int] NOT NULL ,
[QuestID] [int] NOT NULL ,
CONSTRAINT [PK_QAtoQuests] PRIMARY KEY CLUSTERED
(
[QAID],
[QuestID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/* You could add QAID INT NOT NULL to this to have a link from Questionaire if you needed to. */
CREATE TABLE [Answers] (
[SubID] [int] NOT NULL ,
[QuestID] [int] NOT NULL ,
[Answer] [bit] NOT NULL ,
CONSTRAINT [PK_Answers] PRIMARY KEY NONCLUSTERED
(
[SubID],
[QuestID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
January 15, 2003 at 9:40 am
Hi Antares,
I must have been unclear again.
What I want are opinions (pros/cons) about storing 2-12 true/false values as either
- 12 bit columns
- 12 char(1) columns
- packing them together in a varchar(12) column
- packing them together in a bigint column
January 15, 2003 at 11:38 am
- 12 bit columns equal 2 bytes of space. SQL Server will allocate a full byte and let you fill it up.
- 12 char(1) columns equals 12 bytes of space. You have the advantage of doing "Y" and "N" now, though. If you have a non-techie compiling reports, this may be easier on them.
- varchar(12) doesn't get you anywhere, you're still going to need 12 bytes of space, because you can't skip a slot. Plus, you add the bytes to manage the varchar (Steve has an article that talks about this).
- Bigint is 8 bytes = 64 bits. Since you only need 12 true/false, you can use Int (4 bytes = 32 bits) or SmallInt (2 bytes = 16 bits) and do bitwise operations.
The simplest approach is probably the 12 bit or 12 char(1) columns but then you have 12 columns to deal with. However, since you're storing values from a questionnaire, it'll probably be easier if you handle it in this fashion for reporting purposes. My preference has been the bit column, since it's less storage. Just keep in mind that a byte is allocated at a time, even if you have a single bit column.
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 15, 2003 at 11:46 am
Ok, I wrote you a sample VB6 app to demonstrate the saving of the questions answers to one field of type int. The app only saves to the registry, but this is for demonstration of bitwise operations. If anyone wishes I will email them a zipped version of this test harness.
Create Form1.frm with notepad and add this code to it :
VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 6540
ClientLeft = 60
ClientTop = 345
ClientWidth = 9360
LinkTopic = "Form1"
LockControls = -1 'True
ScaleHeight = 6540
ScaleWidth = 9360
StartUpPosition = 2 'CenterScreen
Begin VB.CommandButton Command2
Caption = "Clear Setting"
Height = 315
Left = 4680
TabIndex = 13
Top = 4620
Width = 1695
End
Begin VB.CheckBox Check12
Caption = "Question 12"
Height = 315
Left = 1628
TabIndex = 12
Top = 3945
Width = 6105
End
Begin VB.CheckBox Check11
Caption = "Question 11"
Height = 315
Left = 1628
TabIndex = 11
Top = 3600
Width = 6105
End
Begin VB.CheckBox Check10
Caption = "Question 10"
Height = 315
Left = 1628
TabIndex = 10
Top = 3255
Width = 6105
End
Begin VB.CheckBox Check9
Caption = "Question 9"
Height = 315
Left = 1628
TabIndex = 9
Top = 2910
Width = 6105
End
Begin VB.CheckBox Check8
Caption = "Question 8"
Height = 315
Left = 1628
TabIndex = 8
Top = 2565
Width = 6105
End
Begin VB.CheckBox Check7
Caption = "Question 7"
Height = 315
Left = 1628
TabIndex = 7
Top = 2220
Width = 6105
End
Begin VB.CheckBox Check6
Caption = "Question 6"
Height = 315
Left = 1628
TabIndex = 6
Top = 1875
Width = 6105
End
Begin VB.CheckBox Check5
Caption = "Question 5"
Height = 315
Left = 1628
TabIndex = 5
Top = 1530
Width = 6105
End
Begin VB.CheckBox Check4
Caption = "Question 4"
Height = 315
Left = 1628
TabIndex = 4
Top = 1185
Width = 6105
End
Begin VB.CommandButton Command1
Caption = "Save To DB"
Height = 315
Left = 2985
TabIndex = 3
Top = 4620
Width = 1695
End
Begin VB.CheckBox Check3
Caption = "Question 3"
Height = 315
Left = 1628
TabIndex = 2
Top = 840
Width = 6105
End
Begin VB.CheckBox Check2
Caption = "Question 2"
Height = 315
Left = 1628
TabIndex = 1
Top = 495
Width = 6105
End
Begin VB.CheckBox Check1
Caption = "Question 1"
Height = 315
Left = 1628
TabIndex = 0
Top = 150
Width = 6105
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private mflgLoading As Boolean
Private Sub Command1_Click()
AnswersTotal = GetCheckValue
MsgBox "Saving AnswersTotal =" & AnswersTotal & " to the DB"
'simulate saving to DB
SaveSetting "BITWISEOP", "VALUES", "QUESTIONS", AnswersTotal
SetValues
End Sub
Private Sub Command2_Click()
On Local Error Resume Next
DeleteSetting "BITWISEOP"
SetValues
End Sub
Private Sub Form_Load()
mflgLoading = True
Check1.Tag = Q01
Check2.Tag = Q02
Check3.Tag = Q03
Check4.Tag = Q04
Check5.Tag = Q05
Check6.Tag = Q06
Check7.Tag = Q07
Check8.Tag = Q08
Check9.Tag = Q09
Check10.Tag = Q10
Check11.Tag = Q11
Check12.Tag = Q12
SetValues
mflgLoading = False
End Sub
Private Sub SetValues()
'simulate retrieval from DB
AnswersTotal = CLng(GetSetting("BITWISEOP", "VALUES", "QUESTIONS", 0))
Me.Caption = "Total value = " & AnswersTotal
Check1.Value = Abs(CBool(AnswersTotal And Q01))
Check2.Value = Abs(CBool(AnswersTotal And Q02))
Check3.Value = Abs(CBool(AnswersTotal And Q03))
Check4.Value = Abs(CBool(AnswersTotal And Q04))
Check5.Value = Abs(CBool(AnswersTotal And Q05))
Check6.Value = Abs(CBool(AnswersTotal And Q06))
Check7.Value = Abs(CBool(AnswersTotal And Q07))
Check8.Value = Abs(CBool(AnswersTotal And Q08))
Check9.Value = Abs(CBool(AnswersTotal And Q09))
Check10.Value = Abs(CBool(AnswersTotal And Q10))
Check11.Value = Abs(CBool(AnswersTotal And Q11))
Check12.Value = Abs(CBool(AnswersTotal And Q12))
End Sub
Private Function GetCheckValue() As Long
If mflgLoading Then Exit Function
Dim ctl As Control
Dim lngRet As Long
For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox And IsNumeric(ctl.Tag) Then
If ctl.Value = vbChecked Then lngRet = lngRet Or CLng(ctl.Tag)
End If
Next ctl
GetCheckValue = lngRet
End Function
Private Sub Form_Unload(Cancel As Integer)
AnswersTotal = GetCheckValue
SaveSetting "BITWISEOP", "VALUES", "QUESTIONS", AnswersTotal
End Sub
Create Module1.bas and add this code to it :
Attribute VB_Name = "Module1"
Option Explicit
Public Enum QuestionAnswers
Q01 = 1
Q02 = 2
Q03 = 4
Q04 = 8
Q05 = 16
Q06 = 32
Q07 = 64
Q08 = 128
Q09 = 256
Q10 = 512
Q11 = 1024
Q12 = 2048
End Enum
Public AnswersTotal As QuestionAnswers
Then create a new vb standard project, and remove the existing form1 from it, and add these 2 files in, then run it. It clearly demonstrates that when storing the (true - false) states of several grouped options that a bitwise comparison is the way to go.
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 15, 2003 at 11:56 am
Oh - the pattern for the possible values is last value * 2. So the next value in the enum I have listed is Q13 = 4096, and Q14 would equal 8192 and so on....
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 15, 2003 at 12:23 pm
Ok consider this.
Storing in a varchar.
VARCHAR or any char datatype stores numbers with their ascii value. Which for 0 is 48 and 1 is 49 (you would think someone would have been smart enough to use 0 and 1 but no).
So say you had a questionarie with 8 answers that would be in binary
11111111
so storing it you will end up storing 8 bytes. That number using bitwise ORs is the same as integer 255 which if you were never going to have more than 8 questions a tinyint would be the logical choice and happens to be 1 byte instead.
In addition if the value is stored in a varchar you have to convert you bitmasked value from numeric type to char type and to undo bitmask you have to convert back (keep in mind although you may not explcitly do this, depending on your version of SQL it will implicitly do it and if you change to another version that doesn't do the implicit your code may fail unless you keep all data in the same type). This means additional processing overhead. And varchar has an additional offset that tells the point in the data at which that column begins. So although 8 bytes may be used when stored with the offset it is actually 8 plus 2 bytes so ten altogether.
Storing in an int type.
The value is stored in less bytes based on the maximum size you need. The bitmask can be done to a logical value by using the value position of 1.
In otherwords
0 = 0
1 = 1
2 = 10
4 = 100
8 = 1000
and so on with bitmasking you OR values together to get the common 1 set on. So
3 is 1 and 2 or 11
6 is 4 and 2 or 110
As int types have fixed widths you just need to pick the type that will support the largest bitmask value to represet.
tinyint means 255 or 11111111
smallint means 65535 or 1111111111111111
and so on.
Now the further portion to consider.
Now I hope I explained the difference in storing values so it makes sense.
But consider this. How will you identify the questions a submitter will be given, what the questionaire layout is (the questions) and which ones they answered in a logical relational format. Your concepts are fine except I believe for scalability and custimization. That is why we used the method I presented. I only presented to show you what we do so beyond the char versus int you are really delving into area of personal choice. All have merrits all have cons, for mine I get scalability but I lose in the minimalizing storage needs area.
January 15, 2003 at 12:49 pm
For GROUPS of true-false values you can easily modify the questions while still using bitwise operations. From my example above after setting the Question 2 checkbox to true and several others (pick at random), comment out all refences in the code with check2 in it. Close and re-open. Your existing values will still be set. As far as non grouped questions, and string answers go I am in complete agreement with you Antares, but bitmasking will always be my choice when it comes to storing mutiple grouped boolean values.
See http://www.mvps.org/access/general/gen0038.htm for a nice explanation of bitmasking as my explanation leaves things to be desired.
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 15, 2003 at 1:16 pm
It would be interesting to see what Michael comes back with respect to who's building the reports. That will carry a lot of weight with respect to the optimum design. Bitwise operations and non-techies don't mix.
Also, if we're talking about expanding or contracting questions, Antares solution handles that best because it avoids the issue of data type. Consider that I have to go from 12 questions suddenly to 50. That's a switch from smallint to bigint if I'm storing bitwise in integer-based fields. With Antares' solution you don't have this issue.
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 15, 2003 at 1:31 pm
You're quite correct Brian, my solution is only good for about 31 answers to one
group. And at that time Antares is absolutely correct I would have to switch to
a value per question. I do not disagree with Antares but as a preference I
prefer bitwise. Paste this code below into a empty Form1, I have made it dynamic
as to how many questions it shows. To change the number of questions change the
MAX_ITEMS const to a number less than 31. If you want to see what happens after
31 change it and run.... I usually try to group questions like that onto one page
or array or something similar, similar in fashion to a wizard, so that I can
treat that group as one answer.
Option Explicit
Private Const MAX_ITEMS = 31 'true check box count is 1 less as it is zero based
Public AnswersTotal As Long
Private Sub Command1_Click()
AnswersTotal = GetCheckValue
MsgBox "Saving AnswersTotal =" & AnswersTotal & " to the DB"
'simulate saving to DB
SaveSetting "BITWISEOP", "VALUES", "QUESTIONS", AnswersTotal
SetValues
End Sub
Private Sub Command2_Click()
On Local Error Resume Next
DeleteSetting "BITWISEOP"
SetValues
End Sub
Private Sub Form_Load()
Dim ctl As Control
Dim intX As Integer
Dim intMin As Integer
Check1(0).Tag = 1
intMin = Check1.LBound + 1
For intX = intMin To MAX_ITEMS - 1
Load Check1(intX)
With Check1(intX)
.Caption = "Question " & intX
.Left = Check1(intX - 1).Left
.Top = Check1(intX - 1).Top + Check1(intX - 1).Height
.Tag = CLng(Check1(intX - 1).Tag * 2)
Me.Height = Me.Height + .Height 'increase the form height as ctls are added
.Visible = True
End With
Next intX
SetValues
Command1.Top = Me.ScaleHeight - Command1.Height
Command2.Top = Command1.Top
End Sub
Private Sub SetValues()
Dim ctl As Control
'simulate retrieval from DB
AnswersTotal = CLng(GetSetting("BITWISEOP", "VALUES", "QUESTIONS", 0))
For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox And IsNumeric(ctl.Tag) Then
ctl.Value = Abs(CBool(AnswersTotal And ctl.Tag))
End If
Next ctl
AnswersTotal = GetCheckValue 'this may have changed with less or more check boxes
Me.Caption = "Total value = " & AnswersTotal
End Sub
Private Function GetCheckValue() As Long
Dim lngRet As Long
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox And IsNumeric(ctl.Tag) Then
If ctl.Value = vbChecked Then lngRet = lngRet Or CLng(ctl.Tag)
End If
Next ctl
GetCheckValue = lngRet
End Function
Private Sub Form_Unload(Cancel As Integer)
AnswersTotal = GetCheckValue
SaveSetting "BITWISEOP", "VALUES", "QUESTIONS", AnswersTotal
End Sub
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
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply