SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, October 18, 2006

Multiple bit data type in a table

SQL Apprentice Question
We are going to have a table that store about 300 bit fields (answers to
about 300 yes/no question for a client). I am curious as to whether I need
to split them based on the size of each row. How are mulitiple bit fields
in a table stored in SQL server 2000? Also if each field allows null (or
does not allow), how would that change the size of each row?

Any insight, recommendation, advice would be appreciated.
Thanks



Celko Answers

>> a table that store about 300 bit fields [sic] (answers to about 300 yes/no question for a client). <<


Bits are a low-level assembly language things that are a bitch to do
anything high-level with -- liker stat analysis. Columns are nothing
like fields. You are still thinking in assembly language, not SQL.


>> Any insight, recommendation, advice would be appreciated. <<


Do not design questionaires this way. First of all, there are very few
independent yes/no questions. You are looking for dependencies in the
data; that is the whole point of gathering data.

There are unanswered questions -- people do that! There are
interdependent questions -- "Are you female? No. Have you had cancer of
the uterus? Yes" is clearly wrong and needs a N/A answer. This means a
decision table for the validation rules (you do have those, don't you?)
and flow of questions chart.


Frankly, you probably want to get a questionaire package like RaoSoft
and use it. But if you have to use SQL, then get a normalized schema
and use an encoding for the answers that can be expanded to several
values as needed. Consider multiple choice questions -- nobody like to
do 300 questions.

No comments: