Relational Data Modeling: ER Diagrams and Schema Design Explained with Examples

Relational databases have outlived every technology that was supposed to replace them, and the reason is simple: the relational model is a remarkably good fit for how businesses actually think about their data. But a relational database is only as good as the schema behind it — and designing that schema well is a skill, not an accident.

This post walks through relational modeling end to end: how to build an entity-relationship (ER) model, how to choose keys, how to handle the tricky relationship types, and how to translate the whole thing into tables. It’s aimed at developers and analysts who can write SQL but have never formally designed a schema from scratch.

From Business Statements to an ER Model

Relational modeling starts with entity-relationship modeling, introduced by Peter Chen in 1976 and still the standard way to sketch a schema. The process is refreshingly mechanical once you’ve done it a few times:

1. Entities come from the nouns the business uses: Student, Course, Instructor.
2. Relationships come from the verbs: students enroll in courses; instructors teach courses.
3. Attributes come from the facts: a student has a name and email; an enrollment has a grade.

Take a university enrollment system. The business rules, stated in plain English:

• A student can enroll in many courses; a course has many students.
• Each course is taught by exactly one instructor per term.
• An instructor can teach several courses.

Notice what happened to “students enroll in courses.” That’s a many-to-many relationship, and relational databases cannot store many-to-many directly. It gets resolved into a junction entity — ENROLLMENT — that holds one row per student-course pairing. And conveniently, that junction is exactly where the relationship’s own attributes (term, grade) belong. If you ever find yourself unsure where an attribute goes, ask: does this fact describe the student, the course, or the pairing? A grade describes the pairing.

Cardinality: The Decisions That Shape Everything

Cardinality describes how many of one entity can relate to how many of another, and getting it right is the highest-leverage part of modeling. The three patterns:

One-to-many (1:N) is the workhorse. One customer, many orders. Implemented with a foreign key on the “many” side: orders.customer_id references customers.customer_id.

Many-to-many (M:N) always becomes a junction table, as we saw with enrollments. Watch for junctions that accumulate attributes over time — that’s a sign they’re really a first-class entity in disguise (an “Enrollment” is something the registrar’s office talks about by name).

One-to-one (1:1) is rare and usually deliberate. Legitimate uses: splitting off sensitive columns (employee vs employee_salary with separate access controls), or separating a huge, rarely-read column from a hot table. If you have many 1:1 tables without a reason, they probably belong together.

Beyond the basic pattern, decide optionality: must an order have a customer (mandatory), or can a guest checkout exist (optional)? In an ER diagram this is the difference between || and |o on the connector line. In the database it’s the difference between NOT NULL and nullable on the foreign key. These small marks encode real business policy — surface them for stakeholders to confirm rather than deciding silently.

Choosing Keys: Natural vs. Surrogate

Every table needs a primary key, and there are two schools:

• A natural key is a real-world attribute that uniquely identifies the row: a country’s ISO code, a book’s ISBN.
• A surrogate key is a system-generated value with no business meaning: an auto-incrementing integer or a UUID.

The pragmatic rule most experienced modelers land on: use surrogate keys as primary keys, and enforce natural keys with unique constraints. Natural keys look appealing but betray you in practice — emails get reassigned, ISBNs get reprinted with errors, government IDs get corrected. When a natural key used as a primary key changes, the change cascades into every referencing table. A surrogate key never changes, and the unique constraint still protects the business rule:

CREATE TABLE students (
    student_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,   -- natural key, enforced but not primary
    full_name   TEXT NOT NULL
);

One caveat: for pure junction tables, a composite primary key on the two foreign keys ((student_id, course_id, term)) is often cleaner than adding a surrogate — it enforces “no duplicate enrollments” for free.

Handling the Awkward Cases

Real domains always contain a few structures that don’t map neatly onto boxes and lines. Three you’ll meet constantly:

Self-referencing relationships. Employees have managers who are also employees. Model this with a foreign key pointing back at the same table: employees.manager_id REFERENCES employees(employee_id), nullable for the CEO. The same pattern handles category trees and bill-of-materials structures.

Subtypes. Vehicles can be cars or trucks, sharing some attributes (VIN, make) but not others (payload capacity). You have three options: one wide table with nullable columns (simple, fine for few subtypes), one table per subtype plus a shared parent table (clean, more joins), or one table per subtype with duplicated common columns (avoid — it breaks “one entity, one place”). Choose based on how differently the subtypes behave in queries.

Historical relationships. “Which sales rep owned this account in March?” If accounts.rep_id is simply overwritten on reassignment, the answer is gone. When history matters, promote the relationship to its own table with effective dates: account_ownership(account_id, rep_id, valid_from, valid_to). Deciding which relationships need this treatment is a business conversation, not a technical one.

From ER Diagram to Physical Tables

Translating a finished logical model into DDL is mostly mechanical:

1. Each entity becomes a table; each attribute becomes a column with an appropriate type.
2. Each 1:N relationship becomes a foreign key on the child table.
3. Each M:N relationship becomes a junction table with two foreign keys.
4. Business rules become constraints: NOT NULL, UNIQUE, CHECK (grade IN ('A','B','C','D','F')).

The one non-mechanical part: actually declare the foreign keys. Some teams skip FK constraints “for performance” and rely on application code to keep references valid. Years of orphaned rows later, they regret it. Constraints are the cheapest data-quality tooling you will ever get — the database enforces them on every write, forever, regardless of which application (or which bug) is doing the writing.

A Review Checklist Before You Ship a Schema

• Can you state, in one business sentence, what one row of each table represents? (“One row = one enrollment of one student in one course for one term.”) If you can’t, the table is doing too many jobs.
• Is every many-to-many resolved through a junction table?
• Does every foreign key have a real constraint behind it?
• Have you confirmed optionality (NULL vs NOT NULL) with someone who owns the business rule?
• For each relationship, have you asked “will anyone ever need the history of this?”
• Have you walked the model against the ten most common queries the application will run?

Relational modeling rewards patience up front. A schema that honestly reflects the business will absorb years of feature requests with minor additions — while a schema designed in a hurry gets fought against in every sprint that follows. The next post in this series covers normalization and indexing: how to refine a correct schema into an efficient one.