Web Survey database design

[JG]

I am a developer and I have a problem trying to design a system to
manage data coming from web surveys. Each section can potentially have
dozens of questions, i.e., fields.
I am focusing here only on the table(s) that will hold the survey data.
I do not have any DDL as I am still trying to understand this!
All the examples I have found so far in books and on the web seem to
deal with fairly limited data, that is easily, or so it looks, broken
down in multiple tables.
It seems that, from my research, having a wide table per survey section
with each field as a column, which has been suggested to me, is not
proper design for many reasons - missing values for non-required
questions, table with 100s of possible columns, etc... - so I played
with the idea of a single table where one of the columns would be the
foreign key pointing to a questions table and another column would hold
the data (this is a simplified explanation.)
The problem with this is that now this column will have to accomodate
all types of data, from bits to large varchars, and that field
validation seems now impossible jeopardizing data integrity.

What do knowledgeable designers come up with in this case? Can someone
point me in the right direction?

Jerry

[Erland Sommarskog]

JG (ja****@gmail.com) writes:

I am a developer and I have a problem trying to design a system to
manage data coming from web surveys. Each section can potentially have
dozens of questions, i.e., fields.
I am focusing here only on the table(s) that will hold the survey data.
I do not have any DDL as I am still trying to understand this!
All the examples I have found so far in books and on the web seem to
deal with fairly limited data, that is easily, or so it looks, broken
down in multiple tables.
It seems that, from my research, having a wide table per survey section
with each field as a column, which has been suggested to me, is not
proper design for many reasons - missing values for non-required
questions, table with 100s of possible columns, etc... - so I played
with the idea of a single table where one of the columns would be the
foreign key pointing to a questions table and another column would hold
the data (this is a simplified explanation.)
Yes, I would very much advocate this design.
The problem with this is that now this column will have to accomodate
all types of data, from bits to large varchars, and that field
validation seems now impossible jeopardizing data integrity.

You could make that column sql_variant. This data type can fit any
other SQL Server data type except text, ntext and image.

In order to prevserve integrity, you can for each question define which
datatype that is correct for that question. In the trigger of the answers
table, you can check with sql_variant_property() that the data type is
correct.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[JG]

Erland,

thank you very much for your reply.
It is good to know this is the right way!
I didn't know about the sql_variant type so this is really helpful.
In my search I found a site that also seemed to confirm this design.
The interesting thing is they separate the free text answers from the
'pointer' answers (such as radio buttons, drop-downs, etc...), what
they call 'multiple-choice_questions':
http://www.databaseanswers.org/data_...plex/index.htm

Jerry