designing a table

Hello,

I am developing a database for a school, and l have got all the tables designed, but l have a few questions about the table that will be used to store data for an exam.

The teacher will write the exam and could have 20 questions 25, or 30 questions. This depends on the teacher writing the exam, they could have as many as they want. So how could l develop the database inconsist number of questions? Should l have the maximum number of questions a teacher could have.

The teacher will write the questions and these would be stored in the table. The exam could contain multiple choice or written exams questions. The relationship is a 1 teacher writes many exams. So it is a 1 to many relationship.

How would l design a table with this data. The individual questions could be text datatype, and the multiple choice could be boolean.

Many thanks in advance

Steve

Hi,

Personally I'd do something like the following, and get a nifty screen to deal with the rest.

tblStudents
StudentID - Auto - PK
...

tblTeachers
TeacherID - Auto - PK
...

tblExams
ExamID - Auto - PK
ExamName - Text - 50
CreatedOn - Date/Time
CreatedBy - Number (TeacherID)

tblExamQs
ExamQID - Auto - PK
ExamID - Number - FK
QTypeID - Number - FK
QNo - Number
(MaxNo - Number)
CreatedOn - Date/Time
CreatedBy - Number (TeacherID)

tblExamQsOpts
ExamQOptID - Auto - PK
ExamQID - number - FK
Choice - text - 50
Value - number

tblExamResponses
ExamResponseID - Auto - PK
ExamID - number - FK
StudentID - number - FK
ExamQID - number - FK
WrittenResponse - memo
Ticked - boolean
OptionChoice - number

tblLUQTypes
QTypeID - Auto - PK
QType - text

Extra field to tblexams? (SubjectID - Number (???))

Of course I don't know how the rest of the db is done, and how students will enter results, but that is hopefully a fairly good design.

I see you are in the UK. PM me if you happen to know of any work using Databases (especially Access) for some part time freelance work, I'd be interested :) :D ;)


Vince