Student Results Database Design

[Wally2k5]

Hi,

I'm after a little help with designing a student records databse from scratch - I'm reasonably familiar with Access but can't get my head around this design.

I have multiple students who attend four different courses at our school (they can take each course more than once). During each course they sit multiple exams (and can take each each more than once). What I would like to do is have a database to store:
- student records
- course records
- exam results

So this is the basic design that I am starting with:

Student Table
Student_ID (PK)
Student_Name
Student details....

Course Table
Course_ID (PK)
Course_Type_ID (FK)
Course_Number
Course_Start_Date
Course_End_Date

Exam Table
Exam_ID (PK)
Exam_Name
Course_Type_ID (FK)
Exam_Max_Marks
Exam_Pass_Marks

Exam Results Table
Result_ID (PK)
Student_ID (FK)
Exam_ID (FK)
Result_Mark

Does this sound reasonable? Any thoughts on the design would be very much appreciated!

Wally

 

[spikepl]

Check out http://www.databaseanswers.org/data_models/index.htm

 

[plog]

You're missing a table, one that is going to cause mental headaches: Course_Types. In the Course and Exam tables you have a Course_Type_ID that's listed as a foreign key, but you don't list the table it's the primary key of--I assume it is Course_Types.

I'd really need some sample data for what a course type is to specifically advise, but that's not going to stop me from assuming how you want to use it. I assume it will hold things like "Algebra", "History", "English" etc. If that's true, I don't think you want Course_Type_ID in Exam. I believe Exam should be linked to Course, so Exam should have a foreign key to Course (Course_ID), that way you will know exactly which exam goes to which course. As you have it now, if a student took Algebra 3 times, you wouldn't know what their exams scores were for each course, you'd just see all their exams for Algebra lumped together.

 

[Wally2k5]

Plog,

Thanks for your reply. I have got a Course_Type table as follows:

Course_Type
Course_Type_ID
Course_Type_Name
Course_Type_Notes

This lists the five types of courses we train over the year. Ie Basic Course, Advanced Course etc. Then the Course table lists each of those courses individually, ie Basic Course 1/14, Basic Course 2/13, Advanced Course 1/13 etc.

I thought the exams would be an issue! I tried to replicate the stucture of the courses but see that I can't tell the difference between the result for Student 1 on Exam 1 on Course 1 and Exam 1 on Course 2 etc. What is the best way to solve this? Add the Course_ID to the Exam_Result table?

Thanks.

Wally

 

[plog]

You should replace Course_Type_ID with Course_ID in Exam table.

Also, what does the Course_Number field hold? Is this data attached to a course, or a course type? Is it in the right table?

 

[Wally2k5]

Plog,

The Course_Number is the sequential number to identify that individual course through the year. Ie the first course is Basic Course 1/14, the second Basic Course 2/14 etc.

Do you think I need another table for Exam_Type (one line for each type of exam we run, ie Exam 1 for Basic Courses, Exam 2 for Basic Courses, Exam 1 for Advanced Courses etc)?

So this would set up to feed the Exam table (one entry for each exam on a particular course, Exam 1 on Basic Course 1/14, Exam 2 on Basic Course 2/14 etc) which would in turn feed the Exam_Result table (one entry for each student on each exam, Student 1 on Exam 1 on Basic Course 1/14, Student 1 on Exam 2 on Basic Course 1/14 etc)?

Thanks for your help.

Wally

 

[plog]

I don't think you need Course Number. In Course you have a Course_Type_ID and a date--you can determine which course it is sequentially already. Course Number is redundant and should be eliminated.

No, you do not need Exam Type table. You know which type of exam it is for because it is linked to the Course table, which is linked to the Course_Type table. That information is available at the Exam level as long as you link Courses and Exams.

 

[Wally2k5]

Plog,

Is that true even with multiple exams per course?

I see what you're saying but I'll leave Course_Number in for the moment as that is how we refer to each of the courses.

I'll have a play with the database and see how far I get!

Wally

 

[plog]

Is that true even with multiple exams per course?

Is what true? You can have as many (or no) exams as you like and as long as the Exam table is tied to the Course table you will be able to discern everything you need.