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)
Student can sign up for a course (StudentCourse entity is being created).
Every course is made of lessons, and one lesson can be a part of multiple courses.
Student is able to mark a specific lesson in a specific course as "completed".
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?