Database design: How to model a relationship where a table can belong to either of two other tables?

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 to figure out how best to implement a structure like this within a relational database.

If every "lessons" 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

1 Progress Requirement

a course can either directly have "lessons", or it can have "sections" that in turn have "lessons".   a "lessons" can either directly belong to a "course", or belong to a "section" that in turn belongs to a "course".

1. Although you have given some rules, there are others that are implied, not explicit.  We need to make them explicit.
2. There are other considerations, which need to be evaluated and determined.   Eg. What precisely, is a Lesson ?
   • Is Lesson independent, such that it can be used in more than one course, without duplication of the Lesson ?
     Lesson[Safe Use of Hammer] may be in
     Course[Woodwork] as well as
     CourseSection[Carpentry, Basics]`
   • Is Lesson dependent on Course [and Section], and thus unique to that structure ?
   • I will assume the former.

Here is an interim model, showing the two types of Courses, and an independent Lesson, awaiting decisions, and further data modelling.  Duplicated elements are shown in red.

2 Relational Data Model

Forgive me if I don't show the several intermediate steps (not possible in the demanded simple question and answer format).  Here is the final data model.

• Normalised means no nulls in the database

• Nullable Foreign Keys are particularly bad news, strictly forbidden

• a "dummy record" is not only awkward and horrible (affects every SELECT that accesses the table), it is not a Fact, and it is completely unnecessary

• RecordIds are physical; anti-Relational, each requires an additional column and index.

• This uses Relational Keys (made up from the data), it provides Relational Integrity (as distinct from Referential Integrity)

  • CourseLesson.CourseLesson identifies the lesson within the course, such that it has meaning within the course, independent of the LessonNo
  • SectionLesson.SectionLesson identifies the lesson within the section, such that it has meaning within the course and section, independent of the LessonNo

• Course is an Exclusive Subtype, to ensure that:

  • a simple course Course_TypeLesson has lessons directly, and
  • a sectioned course Course_TypeSection has lessons attached to the section, not the course.

• Lesson is independent and fully defined (once)

  • Lesson is used in either type of Course
  • CourseLesson.AK prevents the Lesson from being duplicated within the simple Course
  • SectionLesson.AK prevents the Lesson from being duplicated within the sectioned Course.

More ?

The progressed data model resolves many issues, and hopefully answers your question.  But it may expose others, it may need further definition: Data Modelling is an iterative task.  Please feel free to comment.

Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993.
• My IDEF1X Introduction is essential reading for those who are new to the Relational Model, or its modelling method. Note that IDEF1X models are rich in detail and precision, showing all required details, whereas home-grown models, being unaware of the imperatives of the Standard, have far less definition. Which means, the notation needs to be fully understood.
• For full definition and usage considerations re Subtypes, refer to Subtype Definition

Answer 2

The simplest way to do this is to have two foreign keys on Lesson:

CourseId null,
SectionId null

Then place a CHECK constraint on the table

(CourseId IS NOT NULL AND SectionId IS NULL) OR (CourseId IS NULL AND SectionId IS NOT NULL)

This means that either one must be present, but not both. Don't be tempted into placing the CourseId on a Lesson belonging to a Section, because this introduces a denormalization in the table: if a Section moved to a different Course, then you would need to update all the child Lessons.

---

Strictly speaking, you should really have a more complex design:
Lesson CourseLesson SectionLesson

This makes it impossible to enforce a Lesson being part of only one or the other though.