Post reply

xml data type vs several records

  • November 18, 2012 at 7:21 am

    #285113

    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

  • November 18, 2012 at 11:52 am

    #1560110

    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.

    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)

  • November 18, 2012 at 3:12 pm

    #1560131

    This is a different question.

    Thanks

  • November 19, 2012 at 5:49 am

    #1560304

    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.

    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 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply