Database structure question
- Using MS-SQL2000. I'm creating a Web form with 10 questions. Each question
can be answered by selecting one of 5 radio buttons (strongly agree, agree,
neutral, disagee, strongly disagree). Once all the questions are answered the
form is submitted to the database. I need help on how to structure the
database.
I need to be able to report results as
|Question| |SA| |A| |N| |D||SD|Question 1 5 2 2 1 3Questiion 2 3 1 3 2 1
I tried creating the table with each question being a column, but since
SQL2000 doesn't support pivot I need another structure?
Your help is greatly appreciated. Thanks
-
Even though SQL Server 2000 doesn't have a pivot function, it's pretty darned easy to make one. So, don't let the lack of a pivot function make you do something crazy like denormalization...
The data you collect for the questions should be normalized like anything else. Something like one of the following (depending on which school you're from or what "standards" have been imposed by the company you work for)...
CREATE TABLE Response
(
SessionID INT, --Or some such to allow grouping of the answers by participant
QuestionID INT,
AnswerID INT, --0 thru 5 (0 - no answer, 1 - SD, 5 = SA)
AnswerDate DATETIME
)
...or...
CREATE TABLE Response
(
ResponseID INT,
SessionID INT, --Or some such to allow grouping of the answers by participant
QuestionID INT,
AnswerID INT, --0 thru 5 (0 - no answer, 1 - SD, 5 = SA)
AnswerDate DATETIME
)
In the first example, the SessionID and QuestionNumber would make up a composit Primary Key. In the second example, the ResponseID would be the Primary Key and the SessionID and QuestionNumber would make up a composite alternate key (which is also unique).
Of course, you'll need a Question table, an Answer table, and a Session table. Note, that if you have a Session table (it should also have a datetime), you may be willing to remove the AnswerDate to further the normalization... while that seems alright to most, removing the AnswerDate column would also remove the ability to figure out how much time each question took to answer (sort of). I'd keep the AnswerDate column provided that you are capturing that datetime of when someone clicks on one of the radio buttons.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff I appreciate your timely response. I am obviously a newbie at this. So I am very green at creating databases. I need to be able to submit all the questions at the same time from the Web form. This is the first design of the table with data examples
EvalID Site EvalDate CoachName Q1 Q2 Q3 Q4 Q5 1 WLK 06/22/2007 Baker 5 4 4 5 4 2 NC 06/24/2007 Becker 5 4 4 4 3 3 NC 06/24/2007 Callahan 5 4 4 4 5 4 NC 06/24/2007 CLARKE 5 4 5 4 5 5 WLK 06/24/2007 Greenfield 4 3 5 3 4 6 COV 06/24/2007 ALEXIS 5 4 5 4 5 7 MMK 06/24/2007 Ambris 5 4 5 4 5 So Q1 is question 1 with one response that the user select from 5 different selections. 5 = strongly agree, 4= agree, 3=neutral, 2= disagree, 1=strongly disagree.
So you see my delima? If the form had just one question I'd be OK, but there are 11 questions. Again thankd for your help!
-
That's why I'm saying you've gotta normalize it... you can submit the answers to the questions all at the same time, just not all in one row. Your current design will become a nightmare the first time you need to add a question.
Collect all the "answers" in an "array" in the GUI, then when you want to submit the answers to the database, write one row from the array to one row in the DB... and each row should be precisely one answer. World of hurt if you do otherwise...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A good developer does not build anything they don't have to. There are a million of freeware survey application out there:
http://www.bestsoftware4download.com/software/t-free-e-data-collection-download-gpuqueua.html
-
Just for starters, here's the table to hold the responses:
Survey_Id Question_Id Response_Id
1102 1 5
1102 2 3
The list of questions goes in another table, same with the list of responses. The you create foreign keys from the table above to the question and response tables.
Bottom line: you never repeat a row in a relational DB (or at least, strive not to).
-
Thanks Jeff. So in collecting the answers in an array and then insert each answer to the DB, would this be a multi-row insert using a loop? I also would have the other information that should go with each answer. like date, site, person, etc. I would assume I could have three forms / threee tables 1. general info, 2. questions, 3. answers with foreign keys that link them together and when submitting the three forms do the loop array with the answers?
-
That's almost exactly correct... it would, indeed, be a multi-row insert using a loop in the GUI. Or, you could store each answer in an answer staging table if you need to.
Also, not sure how you want it done on the GUI, but I would think that you would start each survey/test with a General Info screen to capture the person's info, etc, and screen that you would present one question at a time with (or perhaps, all of the questions if only 10). You would only need the 3rd screen to present answers if you want to show them what they answered v.s. the correct answer.
You are correct about the tables... 1. Person (general info), 2. Question, and 3. Answer with the appropriate links via PK/FK.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply