2

I have the following entities in my database (it's simplified):

  • Student (id)
  • StudentCourse (id, student_id, course_id)
  • Course (id)
  • Lesson (id)
  • LessonCourse (id, lesson_id, course_id)
  • StudentCourseLesson (id, student_id, course_id, lesson_id, completed)

So:

  1. Student can sign up for a course (StudentCourse entity is being created).

  2. Every course is made of lessons, and one lesson can be a part of multiple courses.

  3. Student is able to mark a specific lesson in a specific course as "completed".

The problem:

Should the StudentCourseLesson entities be pre-inserted to the database the moment user signed up for the course with default values (hundreds of rows), or should they be created on the fly, the moment user will start some interaction with the specific lesson of the specific course (like marking one as completed)?

What I can think of:

  • The first option makes my server code cleaner (no need for checks if entity exists in the database) and makes everything in the database existing in the "well defined state" but it causes a massive inserts surge with every sign up, and even if it may not be a problem in this case, I can see how it can quickly become a problem as a design pattern in some more complex relations (bad scalability?).
  • The second option distributes database work in time more, but seems very ugly. It's basically a "lazy creation" of the entities. What to do if I will need to display the list of lessons with their "completed" status? I could as well create them all at this point.

I personally lean towards the first option in my use-case, but I wonder if I didn't miss anything when considering this problem. How this kind of relations should be handled?

  • What is it that makes the second option seem ugly? Just the check if something exists already? How do you handle the case where a student already has taken lesson 42 in a different course and now signs up for a new course that also contains lesson 42? Even in your first option you'll have to check that the student will not have to take the same lesson twice. – Bent Jan 19 '18 at 11:53
  • It's just a feeling so it might be a bad feeling. Student might take the same lesson twice and this is intended. When you have courses for pilot licences for example, a lot of learning material overlap, but they still have to be done. In future I may allow for adding notes to the lesson by a student, so even if the lesson will be the same, the notes might be different based on the context of the specific course. That information would also fell into the StudentCourseLesson entity. – Łukasz Zaroda Jan 19 '18 at 12:00
0

Its a good question - there are complexity trade offs for creation of intersection entities up front vs lazily.

Up front creation:

  • Is cleaner for joins through the intersection entities
  • Saves complexities if intersection entities can be updated in multiple places

Lazy creation:

  • Has benefits if the courses are not immutable. If you want the ability to add lessons, the up front creation would need to maintain multiple code paths for creating intersection entities

  • Saves space and distributes the work (although these might both be trivial)

Note however, that you don't have to create the intersection entity to join through it. You can leverage outer joins to "join" to and through rows that don't exist. In an outer join understanding the driving table of the join becomes important.

I would lean towards lazy creation of intersection entities and use outer joins for the queries. Which way you go will depend on the specific application requirements, as well as how familiar you want to get with outer joins.

0

The first case makes the most sense to me, if and only if a Course is immutable at the point where associated StudentCourses can be inserted.

If a Course can be altered beyond that point - i.e. associated LessonCourses can be inserted and deleted - then lazy creation will be less messy (although still pretty messy).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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