ERD Ternary Relationship – What Would the Cardinalities Look Like?

database-designerd

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):

How to ERD looks for this bit

Now,

  • 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.

Relational representation

My Relational Table looks like this:

Relational Table Schema

The 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.

The question

How do I represent relationship in the ERD with the constraints mentioned above?

Possible Representation

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.

Best Answer

Somewhat unintuitively you should drop the reference to course and just leave the references to user and topic in the Undergoing table. To answer directly your question: there should be only two participating entities so it is not a ternary relationship.

This is one of those interesting cases in normalization where Third normal form (3NF) is fulfilled but Boyce–Codd normal form (BCNF) is not. Both are desirable forms in normal database formalization. What breaks the BCNF is the dependency topic_id->course_id. In plain English: if you know the topic_id you can deduce the course_id also so you don't need an explicit reference.

When you have user_topics relation with references only to user_id and topic_id you can easily fetch the courses of a user with a regular JOIN (via module to course).

Here is more formally put the dependency that is allowed in 3NF but not in BCNF (Third normal form - Wikipedia):

Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)

Boyce–Codd normal form (Wikipedia):

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

Related Question