How to model a course > section > lesson structure where lessons can belong to either sections or courses?

Question

I have three entities:

1. Course
2. Section
3. Lesson

Each "course" is made up of several "lessons". The "lessons" inside a course, can either be categorized into "sections", or not.

So, the contents of a course could look either like this:

Foo Course:
    Lesson 1
    Lesson 2
    Lesson 3
    Lesson 4

Or like this:

Bar Course:
    Section 1:
        Lesson 1
        Lesson 2
    Section 2:
        Lesson 3
        Lesson 4

So, in other words, a course can either directly have "lessons", or it can have "sections" that in turn have "lessons".

From the other perspective, a "lessons" can either directly belong to a "course", or belong to a "section" that in turn belongs to a "course".

I'm struggling with how to implement this structure in a a relational database.

If every "lesson" had to necessarily belong to a "section", it would be easy, I could just simply have a "Course" table, a "Section" table with a "CourseId" column, and a "Lesson" table with a "SectionId" column.

But my scenario is not as straightforward as that. A "section" can potentially exist as a middleman between a "course" and several "lessons", but it can also be absent, in which case a "course" directly has the lessons and no there are no "sections".

I'd appreciate any suggestions regarding how such a structure can ideally be implemented in the context of relational databases.

Thanks.

Answer 1

Generally when I need to handle this sort of situation in textbooks, I “hide” the full structure of a book from the author. So, for your situation, I would have the following structure:

Course
⇢ Section
  ⇢ Lesson

This way, all courses would have a minimum of one section. This offers a course author a bit of flexibility in the future if they choose to switch to (or from) a Course⇢Lesson to Course⇢Section⇢Lesson model. It also drastically simplifies the SQL queries that will be used, as everything is consistent. Other developers will appreciate this when being introduced to your system.

Disclaimer: I write textbook and online learning software for a living, so my design patterns may be biased as a result of experience working with public and private institutions.