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?