Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to save student answers for quiz questions of different types Multiple choice essay Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 3:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 11:31 AM
Points: 74, Visits: 164
I have a table student attempt
Quizid
studentid
questionid
attemptid
studentanswer

If the question is multiplechoice the answer will be a for example
if the question is an essay the answer will be varchar(max)

what data type is student answer should be
Thanks




Post #1385878
Posted Friday, November 16, 2012 4:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 163, Visits: 359
It depends...

If I understand what you are asking:
I would try to control the the amount of space that will be used. Using VARCAR(10000) for example is enough space to get idea across. This will also allow you to do a more solid job of forecasting (of disk space) using a concrete number v. an 'unknown' or guesstimation.

You of course, can use VARCHAR(MAX), but you need to consider "disk space". Depending on the number of students, this could potentially be an issue.

Does that help?
Post #1385881
Posted Friday, November 16, 2012 8:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:44 AM
Points: 15, Visits: 465
I think you should go for xml datatype...if the answer is multiple choice then you may need to store answers like 'a,b,c,d ' ,as per each choice...

It will provide flexibility if you add some more question types in future..

First of all you can define your xml structure for each type..and you can easily retrieve data logically using xquery..

Thnaks,
Saurv
Post #1385895
Posted Sunday, November 18, 2012 7:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 11:31 AM
Points: 74, Visits: 164
thank you
is xml type better than varchar(max) storage wise
Post #1386042
Posted Sunday, November 18, 2012 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
Sarsoura (11/18/2012)
thank you
is xml type better than varchar(max) storage wise


No. It isn't. Not by a long shot. The tags for such flat data stored as XML are totally unnecessary and cause totally unnecessary bloat, resource usage, and other complications. XML, by its very nature, is denormalized data. I know I'll get some pretty good disagreement from others and there are exceptions (this NOT being one of them), of course, but I try to not let anyone store XML or any other type of delimited data in any database.

That, not withstanding, your requirement to store multiple multiple-choice answers could make this a chore one way or another. With the current mindset, you'll either need to shred some XML, split a CSV, or use an EAV style of table where each answer is stored on a separate row. The only perceived advantages that XML might have over the other two methods is that it IS flexible without the complications of an EAV table and, unlike a CSV, it IS indexable by element but I can't see indexes serving much use on the [Student Answer] column in this case. Even if it were to become important, an index on such a column might prove to be of no avail owing to the extremely low number of unique values involved here. Also, there's no requirement for heterogeneous data here. It's either going to be multiple-choice answers or essay-style answers even if it's just a single word to be stored for the essay-style answers.

Although EAVs offer the most normal form of handling the data in this case, they make it a real pain to determine if a multiple choice question that requires more than one answer has all of the correct answers, so I wouldn't go that way. Considering the very limited scope of the problem, I also wouldn't go with the data bloat that XML will cause either. Considering the very limited scope of this problem, even a CSV or other delimited form of storing multiple multiple-choice answers is real overkill that will also cause unnecessary bloat and unnecessary resource usage.

Further, and if it were me, I wouldn't mix the multiple choice answers in the same table as essay-type answers. I'd keep them in separate tables.

Changing the mind-set...

With all of that in mind and with the idea that the multiple-choice answers will be limited to a max of 26 choices (A thru Z but 1 thru 9 could also be used), I'd recommend storing the multiple-choice answers (in sorted order left-to-right if there's more than one) as single letters or digits concatenated in a single VARCHAR(26) column in a table separate from essay answers. This will easily allow for detection of correct answers if they're stored in a similar format. For example, if the correct answer is 1, 3, and 9, then the correct answer would be stored as '139'. Any answer in the [Student Answer] column that equals '139' would be a correct answer for the given question. With this method, there's no need for any splitting or shredding of any type and, therefor, no need for the bloat of XML or delimiters nor the overhead of shredding XML or splitting CSVs just to determine if an answer is correct or not. Storing the answers in sorted order doesn't require rocket science and is quite easy to do both in the [Correct Answer] table and in the [Student Answer] table.

IF there ever comes a need to split out the correct answers, it's almost child's play to split individual characters out of the [Student Answer] column using a Tally Table or similar method especially since there are no delimiters. This would give you the ability to quickly normalize the answers in an EAV style result set for in-depth analysis. It's actually easier to do than it is to shred XML.

So, here's my bottom line recommendation for this problem.

1. Store the correct multiple-choice answers in the [Correct Answer] table as sorted concatenated characters in a VARCHAR(26) column.
2. Store the student answers to multiple-choice questions in the same manner as identified above. This will make finding correct answers a breeze using a simple join with no shredding or splitting involved.
3. Store the multiple-choice and essay question student answers in separate tables since they're totally different types of answers.

Using the method for creating readily-consumable test data outlined in the article located at the first link in my signature line below, if you were to post some test data for the [Student Answer] table, I'd be happy to show you the simplicity of the code necessary to split the [Student Answer] column whose form I've outlined above.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386049
Posted Sunday, November 18, 2012 9:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
Oh, I almost forgot. There's another rather serious complication with the XML datatype in versions of SQL Server prior to 2012 that many table designers just aren't aware of or seem to forget during their design efforts.

One of the advantages of having the Enterprise Edition is that you can rebuild indexes in an ONLINE fashion... but only if the table DOESN'T contain any of the blob datatypes which include XML and VARCHAR(MAX). That's also one of the big reasons why I suggested separate tables for the multiple-choice and essay-style questions especially since the number of essay-style questions will likely be quite low compared to the multiple-choice type of questions.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386054
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse