Database design question (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 12:09
Joined
Jul 19, 2007
Messages
453
I am setting up a student grade reporting database. There will be a Student Master Table (SMT) which will contain personal information about each student, including a unique Student-ID. There will be a Course Master Table which will contain information about each course, including the name and Course-ID. The students, of course, will change each semester, so adding or deleting student records from the SMT is straight forward. The courses will also change periodically, so here's my question:

What table layout would you suggest in order to record the latest grade for each student. This grade update will be done with a Form. I only need the latest grade for each course for each student for this assignment. I'm thinking that there would be a record for each student in this table (call it the Student Grades Table, or SGT), and the fields in the SGT would represent the courses that are available. In this assignment, the same courses would be available for all students, although all students would not take the same courses. Since the courses change over time, and course names can be lengthy, how do I set up field names in order to avoid having to change the field names in the table each time courses are added or deleted?


Or, would you take a different approach to the database tables?


Thanks for your suggestions.
 

plog

Banishment Pending
Local time
Today, 11:09
Joined
May 11, 2011
Messages
11,611
how do I set up field names in order to avoid having to change the field names in the table each time courses are added or deleted?

Field (and table) names should be generic. Specific values should be held in records. From what I see so far, this table would accomplish what you want:

tbl_StudentGrades
sg_ID, autonumber, primary key of table
ID_Student, number, foreign key to tbl_Students
ID_Course, number, foreign key to tbl_Course
sg_Grade, short text/number, this will represent the grade the student got in the course

Since the courses change over time

This doesn't make sense. It really sounds like you've defined 'course' in two ways and are confusing the two. A course is a taught subject which shouldn't change (use the phrase 'New Math' all you want, but Math is, has been and always be Math). It sounds like you are conflating that definition with "enrollment" which is a courses/student permutation. Enrollment can change, courses shouldn't.
 

sumdumgai

Registered User.
Local time
Today, 12:09
Joined
Jul 19, 2007
Messages
453
Thank you. You're right. By course changing, I meant that the courses available to the student may change, so enrollment would change.


So, instead of recording all of the course grades in a single student record, your suggestion creates an individual record for every student/course/grade. Is that right? The grades for each student will be updated using a Form which will list all of the courses and allow posting of grades to each course. That means updating multiple records instead of a single student record. Would you still do the individual record for each student/course/grade?
 

plog

Banishment Pending
Local time
Today, 11:09
Joined
May 11, 2011
Messages
11,611
Data dictates how tables are laid out, not how you want forms to look/operate. So, I would use the table I outlined.

As for data entry, this can easily be done on a form/subform. The main form could be be based on the Course and then in the subform you add all Students/Grades. Or the main form could be based on a Student and then you in the subform you add all their Courses/Grades.

However, after you lay out tables the next step isn't building forms. It's building Reports. No sense making amazingly beautiful forms if you can't get the data out like you want. Tables->Reports->Forms
 

sumdumgai

Registered User.
Local time
Today, 12:09
Joined
Jul 19, 2007
Messages
453
Let's say the student retakes the course and gets a new grade. I only want the last grade achieved. In your outline, how would that be handled? Find existing record, delete it and add new, or find existing record and update it? Sorry for the questions. Just a beginner.
 

plog

Banishment Pending
Local time
Today, 11:09
Joined
May 11, 2011
Messages
11,611
Find existing record, delete it and add new, or find existing record and update it?

Either way, they accomplish the same thing.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 28, 2001
Messages
26,999
If you have a query, you simply want the grade from the record with the MAX date for the given combination of student and course. If there IS only one record, no problem. If there is a retake, you get the most recent grade.
 

Users who are viewing this thread

Top Bottom