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.

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.

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.

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

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?

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.