Designing Databases for Change: A Practical Guide to Database Design for Students

by | Feb 16, 2026 | Case Study, Resources, Schema | 0 comments

learning database design

Role, Relationship, or Status? The Fastest Way Students Improve Database Design

If you’ve ever stared at a problem statement and thought, “I can’t tell what the tables should be,”
you’re not behind — you’re at the real starting line.

Strong database design is the art of deciding what something is in your system:
a role someone plays, a relationship between two things, or a temporary status that changes over time.
That one choice shapes your schema design more than any memorized rule ever will.

“Your schema is your opinion about reality. Make it a good one.”

A Scenario That Traps Students (Until They Learn This)

You’re building a university mentoring program database:

  • Students can be mentors, mentees, or both
  • A mentorship can start, pause, resume, and end
  • A mentor can guide multiple mentees
  • A mentee might switch mentors

Students often try to “store mentoring” inside the Student table. That feels neat… until the timeline shows up.

The One Question That Saves Your Design

Ask this before you draw anything:

Is mentoring something a student is, or something that happens between students?

If it happens, it needs its own place in the model — because things that happen have history.

Bad Design vs Good Design (Cleaner, Not Weird-Looking)

Bad design (crams a timeline into the wrong table):

STUDENTS
- student_id
- name
- is_mentor
- mentor_student_id
- mentorship_start_date
- mentorship_end_date
- mentorship_status

Why it breaks: one student can’t cleanly have multiple mentorships over time, and switching mentors becomes messy.
You’ll end up overwriting history or adding “mentor_2”, “mentor_3” columns (a classic student spiral).

Good design (simple, readable, and adaptable):

STUDENTS
- student_id
- name

MENTORSHIPS

* mentorship_id
* mentor_student_id
* mentee_student_id
* start_date
* end_date
* status

Why it works: mentoring is modeled as a relationship with a lifecycle. Pauses, resumptions, switches, and history
all fit naturally without bending the schema.

Mini Check: What Are You Really Modeling?

For each item, decide if it should be:

  • Role (who someone can act as)
  • Relationship (how two things connect)
  • Status (a changeable condition over time)

Examples: reviewer, subscriber, borrower, team lead.

Design Challenge (Do This Before You Open a Tool)

Write a 3-sentence story using your system:

  1. Someone starts something
  2. Something changes
  3. Something ends or restarts

If your story includes time and change, you probably need a relationship table (not extra columns on an entity).

Turn That Story into ER Diagrams

Sketch the story as an ER diagram first, then convert it into tables.
If you want a clean canvas for ER diagrams while you learn,
try drafting in https://erd.dbdesigner.net.

When the relationships look right, translating into schema design is much easier in
https://www.dbdesigner.net.