473,242 Members | 2,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

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
2 3626
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: GTF | last post by:
PHP Web Survey Idea.. I have been given an opportunity to create a web based survey. This is a fairly lengthy survey of 60 pages on paper (various multiple choice and free form). These are...
0
by: franz | last post by:
Hi developers! Are you using design pattern? And do you want to help me with my research project? Ok you only need two minutes I made a little survey form (8 very short questions). There...
35
by: Mason A. Clark | last post by:
I use Opera (the best browser). In Opera I can switch instantly from the page style sheet to my own, which is: TABLE { border: 1px solid red; } TD { border: 1px dotted red; } Ninety-percent...
5
by: DFS | last post by:
I've written several survey systems in which the majority of the questions have the same or similar responses (Yes/No, True/False, scale of 1 - 5, etc). But this latest survey system I'm working...
4
by: fabiool | last post by:
Hi, We are a systems research group at the Computer Science department at Rutgers University, and are conducting a survey to understand details about network, systems and database...
3
by: Yurk Yurk | last post by:
I need to create a database or something that will allow me to input all of the results of a survey and then search the results for trends and stuff. For example, it might have questions like: ...
12
by: pietlinden | last post by:
Hi, sorry for the vague subject. I am working for a company that finds respondents for surveys and then sends the respondent list and the survey to another company to perform the actual surveys. ...
0
by: Joerg Rech | last post by:
Dear software practitioners, consultants, and researchers, we are currently conducting an international survey about architecture and design patterns. Our goal is to discover how familiar people...
0
by: Joerg Rech | last post by:
Dear software practitioners, consultants, and researchers, we are currently conducting an international survey about architecture and design patterns. Our goal is to discover how familiar people...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.