0

Need an assist on a database design.

I'm creating a system to track students in a course.

Currently, I have courses. Courses are made up of modules. A given module can be in multiple courses (eg 2 courses have the same module, so students from both course A and course B are in module Z.

Students are enrolled in modules. So student john gets enrolled into all the modules that makeup course A.

However, when I put john into module Z, he shows up in course A and course B, even though he should only show in course A.

Currently, I have a student table, a module table, and a course table, linked together with a studentinmodule and moduleincourse table (both many to many).

Any insight into how to redesign so a module can be in multiple courses, but a student enrolled in a module only shows up in one course?

Improve this question
2
  • 1
    Is there a reason you don't link the student directly to the course? Student-->Course-->Module vs Student-->Module-->Course
    – AMtwo
    Commented Apr 14, 2016 at 18:59
  • Initially, yes. Even though a student is in a course, they may not have to take every module in it if they receive credit for that module already. Similarly, we'll also have students who are taking a single module but are not enrolled in the full course. We ended up creating that link though, and adjusting the queries. In the end we won't need to show student to module to course in the same query, so its a non issue. Submit what you put Ill select as approved answer.
    – gr0k
    Commented Apr 14, 2016 at 19:54

1 Answer 1

Reset to default
1

If you link Student-->Course-->Module, you would avoiding having Module Z causing both Course A & B to show up for that student.

Alternatively, you could slightly modify the current to have Student-->(Module & Course). If your Enrollment table includes all three identifiers, it would be trivial to determine if the student is enrolled in Module Z because they are also enrolled in a specific class, or because they are enrolled in the module directly, rather than via a class.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.