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

xml data type vs several records Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 7:21 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
Hello
I am trying to save student answers to questions in a quiz I am looking for best performance and storage space. I do not want the student to get timed out while answering specially essay questions is it better to keep the student answer for each question in a quiz in a separate record or use xml data type to save all the answers to all the questions of the quiz in one field
i.e which of the following is a better design
CREATE TABLE [Quiz_Definition].[quiz_attempts_answers](
[Quizid] [int] NOT NULL,
[attemptid] [int] NOT NULL,
[studentid] [int] NOT NULL,
[layoutseq] [nvarchar](4000) NULL,
[Answers] [xml] NULL,
[timemodified] [datetime] NULL,
CONSTRAINT [PK_quiz_attempts_answers_1] PRIMARY KEY CLUSTERED
(
[Quizid] ASC,
[attemptid] ASC,
[studentid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

where
layoutseq is a list of questions in the order the student saw in this attempt
answers is questionid and studentanswer it includes all questions and answers the student saw in this attempt

vs
CREATE TABLE [Quiz_Definition].[Quiz_Essay_Attempt_Answers_201223](
[QuizID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SectionID] [int] NOT NULL,
[AttemptID] [int] NOT NULL,
[QuestionID] [bigint] NOT NULL,
[AnswerText] [nvarchar](max) NOT NULL,
[TimeModified] [datetime] NOT NULL,
[TimeCreated] [datetime] NOT NULL,
[ModifiedBy] [int] NOT NULL,
[Score] [decimal](8, 2) NOT NULL,
CONSTRAINT [PK_Quiz_Essay_Attempt_Answers_201223] PRIMARY KEY CLUSTERED
(
[QuizID] ASC,
[StudentID] ASC,
[SectionID] ASC,
[AttemptID] ASC,
[QuestionID] ASC)
ie the student answer for each question is saved in a separate record
Post #1386043
Posted Sunday, November 18, 2012 11:52 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:27 AM
Points: 35,769, Visits: 32,437
This is almost a duplicate of the question you posted at the following URL...
http://www.sqlservercentral.com/Forums/Topic1385878-392-1.aspx
... and it has been answered there, as well.

Having multiple posts on the same question only serves to divide the answers of the responders. For anyone that cares to respond, please respond to the post above so we can keep all of the answers together. Thanks.


--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 #1386082
Posted Sunday, November 18, 2012 3:12 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
This is a different question.
Thanks
Post #1386104
Posted Monday, November 19, 2012 5:49 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:27 AM
Points: 35,769, Visits: 32,437
Sarsoura (11/18/2012)
This is a different question.
Thanks


I disagree. It's worded differently but it boils down to the same thing. Which is the best way to store answers to multiple-choice questions that may have more than one correct answer along with essay-style questions. That, not withstanding, it is your post and if you want to say it's different, then we can do that. I'd still post the same answer as I did on the other thread. Ther answer that I posted will prevent timeouts and many other problems as well as giving you the fastest SELECTability for reporting and easiest/fastest/least resource intensive ability to check questions for correct answers.


--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 #1386325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse