I was wondering if anyone could offer a solution to this database design problem. I have very little experience with relational databases in Access. I have only used databases just to get easy data, but now it is starting to get complicated. I read a little on relational database design, but I keep coming into roadblocks with this one. In Access, you can only assign 1 primary key, so I assign this key to the autonumber column because in my other tables it is impossible for me to not have duplicate values.

See, the app I am creating is an online grade book for teachers. Each teacher has several classes, each class has a number of students, each student has a number of assignments, each assignment has a category (homework, classwork, tests, etc.), each assignment has a grade.

This app allows teachers to create a class. The program generates a unique class ID. Then the teacher enters each student one at a time, and the program generates a unique student ID. All four pieces of data are entered into the database.

What I have done is created the following tables. Following the table is the columns, and a small snippet of how it is set up as an example.

Table 1 (Roster): ID--TeacherID--ClassID---StudentID
1 123 234 567
2 123 234 678
3 123 234 789
4 123 235 987
5 123 235 876
6 124 236 567
7 124 236 678
8 124 237 789
9 124 237 987

Notice how each teacher 123, 124 may have the same students but in different classes.

Table 2 (Scores): ID--StudentID--AssignmentID--Score
1 567 000 89
2 678 000 98
3 789 000 90
4 567 001 90
5 678 001 78
6 789 001 85
This table records the student's grade to a particular assignmentID. Not having duplicate values is also not possible, that is why I have the ID column. This table is probably the largest because it records every students scores for each assignment they have done.

Table 3 (Weight): ID--ClassID--CategoryID--Assignment--AssignmentID
1 234 Homework p. 64 000
2 234 Classwork p. 78 001
3 235 Homework p. 34 003
4 235 Homework p. 64 078
5 236 Test Spelling 089
6 237 Classwork p. 120 034
7 237 Test Vocab 064

This table records the assignments teachers give their students, the category, and the classID associated with the assignment. Each assignment is given a unique ID because teachers might give an assignment the same name. So in this table ID and AssignmentID are unique.

The problem I am having is how to write a particular SQL command.
With the tables above, how can I get all the grades for each student in a particular class. I will place this data in a datatable and store it in a dataset and it will be displayed as follows:

Class 234

StudentID "p. 64" "p.78"
567 89 90
678 98 78
789 90 85

Basically, how do I join these tables efficiently, and did I design this database correctly? Any other suggestions on how to solve this problem will be greatly appreciated.

10 Years
Discussion Span
Last Post by dhassen

Most of my database study has been on the theory side so I don't have a lot of experience with real world apps. You'll have to take my idea with a grain of salt.

Following more of an object-oriented design model, I would break the tables up by the objects they represent. This will mean more design in the layout but ultimately, simpler and unambiguous queries.

The objects that you are tracking are the Teachers, Students, Classes, Assignments, and Papers (completed assignments - which is different entity from the issued work). I would layout the tables as: (Bold = Primary Key, Underline = Foreign Key)

Teachers: TeacherID, Name, Office...
Students: StudentID, Name, ...
Classes: ClassID, Name, RoomNumber, ...
Roster: ClassID, StudentID
Assignments: AsgnID, Class, Name, Page, Weight, DueDate, ...
Papers: PaperID, Asgn, Student, Grade, DateSubmitted, ...

The Roster table is needed to make the many-many relationship between Students and Classes. I would not use an additional key field in this case. Although, you can't have two primary keys, you can use two fields to compose the one key. This ensures that each student is enrolled in each class only once.

The structure does allow for a student who is not a member of a class to submit a paper that wasn't assigned to him. From my school days I would say that isn't something you necessarily need to guard against. Other than that, the structure flows naturally from Teachers and Students -> Classes -> Assignments -> Papers. Generating the query your looking for would be straightforward.


That solution looks great, I'm doing somthing similar but slightly more complex..

How could you design the system so a particular assignment could have two different types of grade, for example (in my case) an effort grade, and an attainment grade?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.