Project: Design a Learning Platform Database

Build the mental model

This project pulls together three earlier lessons and asks you to use them at once: tables, keys, and relationships to decide how each entity connects; constraints and normalization to keep data free of duplication; and schema diagrams paired with access patterns to check the design actually serves real queries.

A learning platform like Thuta Learning is a natural fit, because its data model is rich enough to need every one of those tools, yet still small enough to hold in your head at once.

Users - people using the platform
Courses - the courses offered
Lessons - individual lessons inside a course
Enrollments - links a user to a course
Progress - links a user to a lesson's completion
Bookmarks - a lesson a user saved for later

Lessons belong to Courses through a foreign key, so a lesson row carries a course_id instead of repeating the course's title on every row, exactly the duplication the normalization lesson warned against.

Enrollments and Progress are join-style tables linking Users to Courses and Users to Lessons, the same many-to-many pattern the relationships lesson introduced earlier in the course.

What the unique constraint buys you

A unique constraint on (user_id, course_id) inside Enrollments comes straight from the constraints lesson: it stops a user from enrolling in the same course twice, enforced by the database itself rather than by application code that might forget.

Bookmarks reuse that same shape, linking a user to a specific lesson they want to revisit.

Before calling any schema finished, check it against real access patterns: a schema that looks clean on paper but cannot answer the questions your app actually asks is not actually done.

text

LEARNING PLATFORM SCHEMA
------------------------
LEARNING PLATFORM SCHEMA
-------------------------
USERS
  user_id       PK
  name
  email

COURSES
  course_id     PK
  title

LESSONS
  lesson_id     PK
  course_id     FK -> COURSES.course_id
  title

ENROLLMENTS
  enrollment_id PK
  user_id       FK -> USERS.user_id
  course_id     FK -> COURSES.course_id
  UNIQUE(user_id, course_id)

PROGRESS
  progress_id   PK
  user_id       FK -> USERS.user_id
  lesson_id     FK -> LESSONS.lesson_id
  completed

BOOKMARKS
  bookmark_id   PK
  user_id       FK -> USERS.user_id
  lesson_id     FK -> LESSONS.lesson_id

RELATIONSHIPS
  COURSES  1---* LESSONS
  USERS    1---* ENROLLMENTS  *---1 COURSES
  USERS    1---* PROGRESS     *---1 LESSONS
  USERS    1---* BOOKMARKS    *---1 LESSONS

Connect it to a real scenario

Follow this sequence to build the learning platform schema instead of jumping straight to SQL syntax:

List the entities

Write down the six nouns the app talks about: User, Course, Lesson, Enrollment, Progress, Bookmark. Each becomes one table.

Choose primary keys

Give each entity a simple auto-incrementing id: user_id, course_id, lesson_id, and so on.

Wire up foreign keys

Lessons get a course_id FK. Enrollments get both user_id and course_id. Progress and Bookmarks each get user_id and lesson_id.

Add the constraint that matters

Put a unique index on Enrollments (user_id, course_id) so the same enrollment can never be inserted twice.

Check against access patterns

For every access pattern in your list, name the table and key that answers it. No key path means the schema is wrong.

Most drafts fail at this last step

It's easy to model the nouns correctly and still forget the exact question the homepage needs answered every time it loads.

Foreign keys encode relationships; access patterns prove the schema actually works.
Join tables like Enrollments, Progress, and Bookmarks model many-to-many relationships.
A unique constraint enforces a business rule in the database itself, not just app code.

Try the working example

javascript

const schema = {
  Users: { pk: "user_id", fields: ["user_id", "name", "email"] },
  Courses: { pk: "course_id", fields: ["course_id", "title"] },
  Lessons: {
    pk: "lesson_id",
    fields: ["lesson_id", "course_id", "title"],
    fk: [{ field: "course_id", references: "Courses.course_id" }]
  },
  Enrollments: {
    pk: "enrollment_id",
    fields: ["enrollment_id", "user_id", "course_id"],
    fk: [
      { field: "user_id", references: "Users.user_id" },
      { field: "course_id", references: "Courses.course_id" }
    ],
    unique: [["user_id", "course_id"]]
  },
  Progress: {
    pk: "progress_id",
    fields: ["progress_id", "user_id", "lesson_id", "completed"],
    fk: [
      { field: "user_id", references: "Users.user_id" },
      { field: "lesson_id", references: "Lessons.lesson_id" }
    ]
  },
  Bookmarks: {
    pk: "bookmark_id",
    fields: ["bookmark_id", "user_id", "lesson_id"],
    fk: [
      { field: "user_id", references: "Users.user_id" },
      { field: "lesson_id", references: "Lessons.lesson_id" }
    ]
  }
};

const accessPatterns = [
  { name: "Get all lessons for a course", table: "Lessons", via: "course_id" },
  { name: "Get a user's progress across all courses", table: "Progress", via: "user_id" },
  { name: "Get all courses a user is enrolled in", table: "Enrollments", via: "user_id" },
  { name: "Get all bookmarks for a user", table: "Bookmarks", via: "user_id" },
  { name: "Check if a user is already enrolled in a course", table: "Enrollments", via: "user_id+course_id" }
];

function checkAccessPattern(pattern) {
  const table = schema[pattern.table];
  if (!table) return { pattern: pattern.name, supported: false, reason: "table not found" };

  if (pattern.via === "user_id+course_id") {
    const hasUnique = table.unique && table.unique.some(
      (u) => u.includes("user_id") && u.includes("course_id")
    );
    return {
      pattern: pattern.name,
      supported: !!hasUnique,
      reason: hasUnique
        ? "supported via unique constraint on (user_id, course_id)"
        : "no supporting constraint"
    };
  }

  const fkFields = (table.fk || []).map((f) => f.field);
  const supported = fkFields.includes(pattern.via) || table.fields.includes(pattern.via);
  return {
    pattern: pattern.name,
    supported,
    reason: supported
      ? `supported via foreign key "${pattern.via}" on ${pattern.table}`
      : "no supporting key"
  };
}

accessPatterns.forEach((p) => {
  const result = checkAccessPattern(p);
  console.log(`${result.supported ? "OK" : "MISSING"} - ${result.pattern}: ${result.reason}`);
});

You should see

OK - Get all lessons for a course: supported via foreign key "course_id" on Lessons
OK - Get a user's progress across all courses: supported via foreign key "user_id" on Progress
OK - Get all courses a user is enrolled in: supported via foreign key "user_id" on Enrollments
OK - Get all bookmarks for a user: supported via foreign key "user_id" on Bookmarks
OK - Check if a user is already enrolled in a course: supported via unique constraint on (user_id, course_id)

5-minute try-it

Extend the schema with a Certificates table: a user earns a certificate for a course once every lesson in that course has progress marked completed. Decide its primary key and foreign keys, add it to the access-pattern list, then re-run the validation function against the new pattern 'get all certificates for a user.'

One important caution

Designing tables before checking what queries the app will actually run, then discovering a key report needs three unindexed joins.

Skipping the unique constraint on Enrollments and relying on application code to prevent duplicate enrollments, which breaks the first time two requests race.

PostgreSQL Documentation: Constraints — How Databases Work