How to Design a Database Schema

Build the mental model

Designing a database schema is not a single leap of insight — it is a repeatable sequence of decisions. Treating it that way is what separates a schema that survives production from one that gets rewritten six months later.

Instead of jumping straight into tables and columns, experienced teams work through the same nine stages every time — from gathering requirements to testing the finished design against real data.

1. Requirements

Write down what the system must do in plain language: users browse and enroll in courses, instructors create lessons, the app tracks completion.

2. Identify Entities

Pull out the nouns worth their own table: User, Course, Lesson, Enrollment, Progress, Bookmark.

3. Define Fields

Give each entity its attributes: User gets email and name; Lesson gets title, order, and content.

4. Choose Keys

Give every table a primary key (usually an id), and use foreign keys so Lesson can point back to its Course.

5. Define Relationships

One Course has many Lessons; a User and a Course connect through an Enrollment; a User has many Progress rows.

6. Add Constraints

Require an email on User, make it unique, and require a Lesson to always belong to a Course.

7. Design Queries

Write out the real queries the app runs: get all lessons for a course, get a user's progress across a course.

8. Add Indexes

Index the foreign keys those queries filter on, such as course_id on Lesson and user_id on Progress.

9. Test

Load realistic data and run the actual queries, checking that results are correct and reasonably fast.

Notice that queries and indexes come near the end, not the beginning — you cannot pick good indexes until you know what the application will actually ask the database to do.

text

SCHEMA DESIGN WORKFLOW
----------------------
1. Requirements    -> what must the system do?
        |
2. Identify Entities -> User, Course, Lesson, Enrollment...
        |
3. Define Fields    -> attributes per entity
        |
4. Choose Keys      -> primary keys, foreign keys
        |
5. Define Relations -> one-to-many, many-to-many
        |
6. Add Constraints  -> required, unique, not null
        |
7. Design Queries   -> what will the app actually ask?
        |
8. Add Indexes      -> speed up those specific queries
        |
9. Test             -> real data, real queries, verify

Connect it to a real scenario

Applying the workflow to a learning platform makes it concrete. From a handful of plain-English requirements, entities emerge directly: User, Course, Lesson, Enrollment, Progress, and Bookmark.

Easy to miss

Enrollment and Progress look like plain relationships at first glance. Each needs its own fields (an enrollment date, a completion timestamp), which is what earns a table of its own rather than just a foreign key.

Constraints add real-world rules on top of the raw structure: a Lesson must belong to exactly one Course, and a user should not be able to enroll in the same course twice.

The code example below applies a simplified version of step two — turning a list of requirements into a starting list of candidate entities. It is intentionally basic; a real session still needs a human to review, merge, and rename what it finds.

Schema design is a repeatable nine-step workflow, not a single flash of insight.
Queries and indexes come near the end of the process, once real entities, keys, and relationships exist.
Entities that look like plain relationships (like Enrollment or Progress) often deserve their own table once you check for extra fields.

Try the working example

javascript

function extractCandidateEntities(requirements) {
  // Illustrative heuristic only -- NOT real NLP. It just looks for known
  // domain-noun keywords so you have a starting list to refine by hand.
  const knownNouns = {
    user: "user", users: "user",
    course: "course", courses: "course",
    lesson: "lesson", lessons: "lesson",
    enrollment: "enrollment", enrollments: "enrollment",
    progress: "progress",
    bookmark: "bookmark", bookmarks: "bookmark",
    instructor: "instructor", instructors: "instructor"
  };
  const nounPattern = new RegExp("\\b(" + Object.keys(knownNouns).join("|") + ")\\b", "gi");
  const counts = {};
  for (const req of requirements) {
    const matches = req.match(nounPattern) || [];
    for (const m of matches) {
      const key = knownNouns[m.toLowerCase()];
      counts[key] = (counts[key] || 0) + 1;
    }
  }
  return Object.entries(counts)
    .sort((a, b) => b[1] - a[1])
    .map(([entity, mentions]) => ({ entity, mentions }));
}

const requirements = [
  "Users can browse and enroll in courses",
  "Each course has multiple lessons in a fixed order",
  "The app tracks a user's progress through each lesson",
  "Users can bookmark a lesson to return to later",
  "Instructors create courses and lessons",
  "An enrollment links one user to one course"
];

console.log(JSON.stringify(extractCandidateEntities(requirements), null, 2));

You should see

The function scans the six requirement strings and counts each domain keyword it recognizes, returning a candidate entity list sorted by mention count: user (4), course (4), lesson (4), progress (1), bookmark (1), instructor (1), enrollment (1). It correctly avoids the classic 'progress' -> 'progres' stemming bug by mapping known singular and plural forms explicitly instead of blindly stripping a trailing 's'.

5-minute try-it

Add a new requirement string like "Instructors can leave feedback comments on a lesson" to the requirements array, then run the code again. Which new entity appears? Decide whether it deserves its own table by checking whether it needs fields beyond a simple foreign key.

One important caution

Jumping straight to tables and columns before writing down requirements, which produces a schema that fits nobody's actual questions.

Designing indexes speculatively before any real queries exist, instead of waiting until step seven reveals what the application actually needs.

Wikipedia: Database design — How Databases Work