467,122 Members | 1,319 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,122 developers. It's quick & easy.

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

Aug 25 '05 #1
  • viewed: 3403
Share:
2 Replies
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

Aug 26 '05 #2
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

Aug 30 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by GTF | last post: by
35 posts views Thread by Mason A. Clark | last post: by
4 posts views Thread by fabiool@gmail.com | last post: by
3 posts views Thread by Yurk Yurk | last post: by
12 posts views Thread by pietlinden@hotmail.com | last post: by
reply views Thread by Joerg Rech | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.