Results 1 to 6 of 6

Thread: Database Design

  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Database Design

    I'm new to the concept of relational database design, and I was wondering if anyone had any suggestions for a schema on a project I'm working on.

    I have to design a database to accept the input from an academic evaluation form, but the problem is many of the questions on the form change from year to year.

    I was thinking that I could create a "response" table flexible enough to accept the data from each year, and then create question tables for each year (e.g. 2001questions, 2002questions etc.). When I displayed the data I could match up "response_1" from a 2001 evaluation with "question_1" from the 2001questions table. Can anyone think of a better way to do this? Thanks.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You should normalize Year out of Question. No need for a table for every year. Response is the product of a combination of a question and a student.

    Year(YearID, YearName)

    Question(QuestionID, YearID, QuestionText)

    Student(StudentID, StudentName)

    Response(StudentID, QuestionID, ResponseText)

    Is one possible model.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    I agree. Each evaluation_form consists of a series of questions, each numbered sequentially. All of the questions have a sequence-number (to keep them in order) and the form_id of the exam they belong to.

    Each completed_evaluation has a similar one-to-many format. First there is the evaluation-record (which describes who gave the response, and what form_id it was. Then there is a table of question_responses each linked to the completed_evaluation that owns it and to the question_id (thence to the evaluation_form) of the question being answered.

    So you have two sets of tables with 1->Many relationships. The first describes forms and their questions. The second describes responses and the question-by-question answers given to them.

    Any number of forms are supported, with any number of questions each. And any number of responses can be stored too.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Posts
    13

    Re: Database Design

    Originally posted by stonkin
    I'm new to the concept of relational database design, and I was wondering if anyone had any suggestions for a schema on a project I'm working on.

    I have to design a database to accept the input from an academic evaluation form, but the problem is many of the questions on the form change from year to year.

    I was thinking that I could create a "response" table flexible enough to accept the data from each year, and then create question tables for each year (e.g. 2001questions, 2002questions etc.). When I displayed the data I could match up "response_1" from a 2001 evaluation with "question_1" from the 2001questions table. Can anyone think of a better way to do this? Thanks.

    Hi

    I would suggest you to normilization to 3rd normal form
    but in 3 normal form number of tables will increase hence number of join will increase which will lead to slow data retrive
    but there will be no duplicate records in third normal form
    If you gurantee that your business will not change then you can go for
    star schema in this case data retrive will be bit fast compare to third normal form

    regards
    nanaiah
    Mumbai India
    AUM SAI RAM

  5. #5
    Join Date
    Dec 2003
    Posts
    4
    Thanks so much for the responses.

    Sundial, I think I understand what you're saying, but I'm not sure what a schema following those two one to many relationships would look like. Could you describe it in more concrete terms?

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think what sundialsvcs is telling you has the following structure

    Year(YearID, YearName)

    Form(FormID, YearID, FormName)

    Question(QuestionID, FormID, QuestionNumber, QuestionText)

    The above allows more than one form.

    Student(StudentID, StudentName)

    Response(ResponseID, StudentID, FormID)

    Answer(ResponseID, QuestionID, AnswerText)

    The above allows more than one response but only one set of answers per response.
    Last edited by certus; 01-03-04 at 18:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •