2

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.

1 Answer 1

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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