There are four entity types in the business domain of relevance: USER, COURSE, MODULE and TOPIC.
The business rules go something like this:
- A Course contains one or more Modules, and
- each Module contains one or more Topics.
So, I have drawn the following entity-relationship diagram (ERD):
- a User can study many Courses, and
- a Course can be taken by many Users.
I want to maintain the progress of a user in that course and so I'm storing an instance of Topic in the studies relationship.
My Relational Table looks like this:
topic_id column in this table stands for the Next topic the user needs to complete. So whenever the user enrolls for a course I fetch the first topic of the first module of the course and update the row. So
(user_id, course_id) becomes my composite primary key. Also, when a user completes a topic, I fetch the next topic in the list and update the instance.
How do I represent relationship in the ERD with the constraints mentioned above?
Since there are three participating entity types, does this make this a ternary relationship, and, if so, what should the cardinalities be like?
Any insight would be much appreciated.