Schema Diagrams and Designing from Access Patterns

Build the mental model

A schema diagram is a map of a database: boxes for tables, a line marking each primary key, and arrows showing which foreign keys point where. Reading one quickly is a core skill.

The learning platform's diagram below shows Users, Courses, Lessons, Enrollments, Progress, and Bookmarks, with every foreign key drawn as an arrow back to the table it references.

• "Get all lessons for a course, in order" -> needs an index on Lesson.course_id (and a sort by order).
• "Get a user's progress across a course" -> needs an index on Progress.user_id (or a combined index with course).
• "Find a course by its URL slug" -> needs a unique index on Course.slug, not just the primary key.
• "Get a user's bookmarks" -> needs an index on Bookmark.user_id, or every lookup scans the whole table.

LEARNING PLATFORM SCHEMA
------------------------
USERS                     COURSES
+------------+            +-------------+
| PK id      |            | PK id       |
|    email   |            |    slug     |
|    name    |            |    title    |
+------------+            +-------------+
      |  \                      |
      |   \                     |
      |    \                    v
      |     \             LESSONS
      |      \            +-------------+
      |       \           | PK id       |
      |        \--------->| FK course_id|
      |                    |    order    |
      |                    +-------------+
      v                          ^
ENROLLMENTS                      |
+---------------+                |
| PK id         |                |
| FK user_id    |                |
| FK course_id  |          PROGRESS
+---------------+          +---------------+
                            | PK id         |
BOOKMARKS                   | FK user_id    |
+---------------+           | FK lesson_id  |
| PK id         |           +---------------+
| FK user_id    |
| FK lesson_id  |
+---------------+

Connect it to a real scenario

Start from the diagram, but finish from the access patterns. Write out the exact queries the learning platform's screens need — the course page, the dashboard, the course-detail page, the saved-items page.

Each pattern is a small test the schema must pass. Filtering without an index means a full table scan that gets worse as data grows, not better — these are not hypothetical, they are the filters production code runs on every request.

The code example below encodes that test as a function: it takes a candidate schema and a list of access patterns, and reports which ones the schema already answers efficiently, which need an index, and which need a schema change.

• A schema diagram maps tables, primary keys, and the foreign keys connecting them — read it before writing any query.
• Design from access patterns: list the real queries the app will run before finalizing fields and indexes.
• A perfectly normalized schema can still perform badly if it was never checked against real query patterns.

Try the working example

function checkAccessPatterns(accessPatterns, schema) {
  return accessPatterns.map((pattern) => {
    const entitySchema = schema[pattern.entity];
    if (!entitySchema) {
      return { ...pattern, verdict: "schema change needed (entity does not exist)" };
    }
    if (!entitySchema.fields.includes(pattern.filterField)) {
      return { ...pattern, verdict: "schema change needed (field missing)" };
    }
    if (!entitySchema.indexes.includes(pattern.filterField)) {
      return { ...pattern, verdict: "needs an index" };
    }
    return { ...pattern, verdict: "efficient" };
  });
}

const accessPatterns = [
  { name: "Get all lessons for a course", entity: "lessons", filterField: "course_id" },
  { name: "Get a user's progress", entity: "progress", filterField: "user_id" },
  { name: "Find a course by slug", entity: "courses", filterField: "slug" },
  { name: "Get a user's bookmarks", entity: "bookmarks", filterField: "user_id" }
];

const schema = {
  lessons: { fields: ["id", "course_id", "title", "order"], indexes: ["id"] },
  progress: { fields: ["id", "user_id", "lesson_id", "completed_at"], indexes: ["id", "user_id"] },
  courses: { fields: ["id", "slug", "title"], indexes: ["id"] },
  bookmarks: { fields: ["id", "user_id", "lesson_id"], indexes: ["id"] }
};

const results = checkAccessPatterns(accessPatterns, schema);
for (const r of results) {
  console.log(r.name + " -> " + r.verdict);
}

Checked against the sample schema, three of four access patterns come back "needs an index" (lessons by course_id, courses by slug, bookmarks by user_id all lack an index on that field), while "Get a user's progress" comes back "efficient" because user_id is already indexed on Progress. That mix is intentional: it shows the checker catching real gaps instead of reporting everything as fine.

MDN: Structuring related data — How Databases Work