How to Create an ER Diagram

A good ER diagram is not drawn, it is derived. Start from what the application must remember, and the schema falls out almost mechanically. Here is the method, step by step.

1. List the nouns

Write down what the application stores. "A booking app: users reserve properties, each reservation has payments and a review." The nouns are users, properties, reservations, payments, reviews — those are your candidate tables.

Expect to throw a few nouns away. "Homepage", "dashboard", "the admin" are not entities — nothing persists about them. Others are the same thing under two names: "listing" and "property" collapse into one table. The rule is simple: an entity earns a box only if the database must remember it independently of any other row.

2. Give every entity a primary key

In 95% of cases, an auto-incrementing integer id (or a UUID) is the right primary key. Natural keys like emails look tempting but change in real life; a surrogate id never does.

The remaining 5% is join tables, where the natural key is the pair of foreign keys itself — (student_id, class_id) is a perfectly good primary key because a duplicate pairing is meaningless. UUIDs (gen_random_uuid() in PostgreSQL) are the choice when rows are created on several servers or offline clients, at the cost of slightly larger indexes.

3. Spot the verbs — they are your relationships

• One user reserves many properties → foreign key on the many side.
• A reservation belongs to one property and one user → two foreign keys on reservations.
• Users can review several properties, and a property has several reviews → reviews is itself the join table, carrying the rating.

For each verb, ask the two questions that fix the cardinality: can there be more than one on each side? Can a row exist without the other side at all? The answers decide where the foreign key goes and whether the column is NULL.

4. Add attributes with real types

For each table, list its columns and pick honest types: varchar(255) for bounded strings, text for free content, timestamp for instants, decimal(10,2) for money (never float), boolean for flags. Mark NOT NULL what must always exist.

Two habits pay for themselves here. First, give every table created_at TIMESTAMPTZ NOT NULL DEFAULT now() — you will want it eventually and backfilling is painful. Second, keep amounts and their currency together: a price without a currency column is a bug you ship to international users.

5. Resolve many-to-many explicitly

Any time two entities can each be linked to many of the other, draw a join table in the middle. student × class → enrollments. post × tag → post_tags. The join table's primary key is usually the pair of foreign keys.

Name join tables after the relationship, not after the tool: enrollments, reservations, post_tags — never table1_table2. If the relationship later gains data (an enrollment gets a grade, a reservation gets a total), the columns land in the join table and nothing else moves.

6. Validate, then draw it

Walk through the main use cases against your draft: place a reservation, refund a payment. If a use case has nowhere to write its data, a table is missing. Then produce the visual — paste the SQL into ERDGenerator and get the diagram instantly, or describe the app in plain English and start from a generated proposal.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  owner_id INTEGER NOT NULL REFERENCES users(id),
  title VARCHAR(150) NOT NULL
);

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  property_id INTEGER NOT NULL REFERENCES properties(id),
  check_in DATE NOT NULL,
  check_out DATE NOT NULL
);

Finishing the example: payments and reviews

Extend the skeleton with the two remaining nouns and the design questions get sharper. A reservation can have several payments (deposit, balance, refund) — 1:N, foreign key on payments. Can a user review a property without a reservation? That is a product decision, and the schema expresses it: reviews.reservation_id is NULL only if you decided walk-in reviews are allowed.

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  reservation_id INTEGER NOT NULL REFERENCES reservations(id),
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0),
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  reservation_id INTEGER UNIQUE REFERENCES reservations(id),
  property_id INTEGER NOT NULL REFERENCES properties(id),
  rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT
);

The UNIQUE on reviews.reservation_id encodes a business rule as a 1:1 relationship: one review per reservation, enforced by the database rather than by hoping the application remembers. This is the kind of decision a diagram makes obvious — the edge shape says the rule out loud.

Common mistakes

• Designing around the current screen instead of the data. Screens change every sprint; the schema outlives them all.
• Storing a list in a column (tags VARCHAR — "a,b,c"). It cannot be indexed, joined or constrained. That is always a missing join table.
• Forgetting the delete story. Every FK needs a decision: CASCADE, SET NULL, or RESTRICT. Defaults rarely match the business.
• One giant table with nullable columns for "maybe later" (a users table with shop_name, seller fields...). Split when a role has its own attributes.
• Skipping uniqueness where the business promises it — two rows with the same (user_id, property_id, check_in) means double bookings.
• Adding foreign keys "after launch". The day you try is the day you discover the orphans already in production.

Best practices

• Derive the diagram from the requirement text, then let the SQL fall out of the diagram — never edit the picture and the code independently.
• Enforce business rules with constraints (UNIQUE, CHECK, FK) whenever possible; the database is the last line of defense.
• Prefer RESTRICT over CASCADE by default, and choose CASCADE deliberately per relationship — deleting a user should not silently delete payment history.
• Keep names singular and consistent (user, not users on one table and accounts on another).
• Version the schema in migrations and regenerate the diagram from them, so documentation never lies.
• Review the diagram with the team before writing the first migration; it is the cheapest moment to find a mistake.

Frequently asked questions

How do I handle attributes I might add later?

You usually do not. Adding a column is a cheap, safe migration in every major database; pre-adding nullable columns for hypothetical features is how schemas rot. Model what is real today, and let the join tables and columns appear when the feature does.

What about soft deletes?

If the product needs to undelete things or keep an audit trail, model it: a deleted_at TIMESTAMPTZ column, plus partial unique indexes (UNIQUE WHERE deleted_at IS NULL) where uniqueness must ignore deleted rows. Draw them like any other column — a soft delete changes the meaning of every query against the table, so it belongs on the diagram, not in a footnote.

Can I start from a plain-English description?

Yes. Describe the domain the way you would to a colleague, and a tool like ERDGenerator can propose tables, keys and relationships from it. Treat the proposal as a first draft of step 1 — you still apply steps 2 through 6 yourself.