Visualising a database before writing the schema

Most databases are designed in the migration file. Someone opens an editor, writes CREATE TABLE users, adds the columns they can think of, and moves on to the next table. The structure that emerges is the sum of a series of local decisions, and nobody looks at the whole until a report needs data the schema cannot provide.

Drawing the database first reverses that order. You decide what the data is about and how it connects, then you decide columns, then you write SQL. The diagram takes less time than the first migration and catches the expensive mistakes while they are still cheap to fix. This article sets out the workflow step by step and applies it to a booking system.

Why the diagram comes first

SQL is a good language for defining tables and a poor one for seeing them. A schema of twelve tables is several hundred lines of text, and the relationships between them are scattered across REFERENCES clauses that you have to assemble in your head. A missing join table is not a syntax error. A foreign key on the wrong side runs perfectly well.

A diagram shows structure at a glance. Lines that cross the whole page suggest a table in the wrong place. A table with no lines is either a lookup table or a mistake. Two tables connected by a line with "many" at both ends cannot be built as drawn. None of this needs expertise to notice; it needs the structure to be visible.

The cost argument is simple. Moving a box is free. Moving a column out of a table that already holds production data needs a migration, a backfill and usually a deployment plan. Every structural decision made on the diagram is one you do not have to make in a migration.

Step one: nouns to entities

Start with a plain-language description of what the system does, ideally in the words of the people who will use it, and underline the nouns. Each noun is a candidate entity. Most will survive; some will turn out to be attributes, and some will merge.

Three questions sort the candidates:

• Does it have facts of its own? A customer has a name and an email, so it is an entity. A "confirmation" might just be a timestamp on something else.
• Can there be more than one for the same parent? A customer has one email address, so email is an attribute. A customer can have several bookings, so booking is an entity.
• Does it exist independently? A meeting room exists whether or not it is booked. That makes it an entity even if it only has a name.

Put each surviving noun on the canvas as an empty box with just a name. Resist adding columns. At this stage the boxes are placeholders for decisions you have not made yet.

Step two: relationships before columns

Connect the boxes before filling them. For each pair that relates, draw a line and label it with a verb: a customer makes bookings, a booking is for a resource. Then add cardinality to both ends.

Doing relationships first matters because relationships decide columns. A one-to-many relationship means a foreign key on the many side; you cannot place that column correctly until you know which side is which. If you start with columns, you tend to put a customer_name on the bookings table because it seemed useful at the time, and then the relationship arrives later as a second, conflicting source of truth.

Ask each relationship question in both directions, out loud if it helps. "One customer, how many bookings? One booking, how many customers?" Write the answers on the line. The second question is the one people skip, and it is the one that finds the next problem.

Step three: find the many-to-many early

Whenever both directions come back as "many", you have a many-to-many relationship, and it cannot be stored as drawn. It needs a third table between the two, holding a foreign key to each side.

This is the single most valuable thing the diagram finds, for two reasons. First, a missed many-to-many is the most expensive schema mistake to fix later, because it means splitting an existing foreign key into a new table and migrating every row. Second, the join table often turns out to be an important entity in its own right. Students and courses are many-to-many; the table between them is enrolments, which has a term and a grade and is what most of the school's reports are actually about.

A useful habit: whenever you draw a join table, ask what else is true about the pairing. When did it start? Who created it? Does it have a status? If the answer to any of these is a real value, the join table is an entity and deserves a proper name.

Step four: names that will survive

Names chosen on the diagram end up in SQL, in the code that queries it and in every conversation about the system, so it is worth a few minutes. Pick a convention and apply it to every box before writing any SQL.

Use the business's words where they exist. If the staff say "resource" for anything bookable, call the table resources, even if "rooms" feels more concrete. A schema that matches the vocabulary of the people using it saves translation in every meeting.

Mistakes a diagram reveals and SQL hides

Each of the following is valid SQL. Each is obvious on a diagram once you know what to look for.

The last row deserves an example. In a CRM, contacts belong to companies and deals belong to companies. If activities also linked directly to a company, the schema would have two routes from an activity to a company (directly, and via its deal), and nothing would stop them pointing at different companies. Drawn, that shows up as a closed loop of lines. The fix is to remove one route and derive it from the other.

Worked example: a booking system

A small coworking space wants online booking for its meeting rooms and desks. The manager's description: customers book a room or a desk for a time slot. Rooms are sometimes closed for cleaning or private events. Two people must never end up with the same room at the same time, including when both are halfway through paying.

Nouns: customer, room, desk, booking, time slot, closure, payment. Sorting them with the three questions:

• Customer and booking are entities.
• Room and desk behave identically for booking purposes, so they merge into one entity, resources, with a capacity column.
• Time slot has no facts of its own beyond a start and end, so it becomes two columns on bookings, not a table.
• Closure has a start, an end and a reason, and a resource can have many, so it is an entity: blackouts.
• Payment matters, but it belongs to a payment provider. For this design it is out of scope, noted in the margin.

Relationships, asked both ways. One customer, many bookings; one booking, one customer. One resource, many bookings; one booking, one resource. One resource, many blackouts; one blackout, one resource. No many-to-many, which is worth confirming rather than assuming: if one booking could cover several rooms, bookings and resources would need a join table. The manager confirms that a booking is always for one room.

The hard requirement is the last sentence of the description. Someone choosing a slot and then entering card details needs that slot held for a few minutes. A separate holds table would mean checking two tables for every availability query. Instead, a hold is a booking with an expires_at value; a confirmed booking has expires_at set to NULL. One table, one place to check.

From diagram to CREATE TABLE

With the diagram settled, the SQL is mostly transcription. Work through the tables in dependency order: parents first, so every REFERENCES clause points at a table that already exists. For each box, write the columns, then go back over the lines and decide three things the diagram may not show: nullability, defaults and extra constraints.

CREATE TABLE customers (
  id     uuid PRIMARY KEY,
  email  varchar(255) NOT NULL UNIQUE,
  name   varchar(120) NOT NULL
);

CREATE TABLE resources (
  id        uuid PRIMARY KEY,
  name      varchar(120) NOT NULL,
  capacity  int NOT NULL CHECK (capacity > 0)
);

CREATE TABLE bookings (
  id           uuid PRIMARY KEY,
  customer_id  uuid NOT NULL REFERENCES customers(id),
  resource_id  uuid NOT NULL REFERENCES resources(id),
  starts_at    timestamp NOT NULL,
  ends_at      timestamp NOT NULL,
  expires_at   timestamp NULL,  -- set while held, NULL once confirmed
  CHECK (ends_at > starts_at),
  UNIQUE (resource_id, starts_at)
);

CREATE TABLE blackouts (
  id           uuid PRIMARY KEY,
  resource_id  uuid NOT NULL REFERENCES resources(id),
  starts_at    timestamp NOT NULL,
  ends_at      timestamp NOT NULL,
  reason       varchar(200) NOT NULL,
  CHECK (ends_at > starts_at)
);

A few decisions in that SQL came from the diagram and a few did not. Every foreign key is NOT NULL because every line on the diagram is mandatory on the parent side. The UNIQUE constraint on resource and start time is what stops double booking when slots have fixed start times. If bookings can be any length and overlap arbitrarily, a unique constraint is not enough; PostgreSQL, for example, offers exclusion constraints that reject overlapping time ranges.

One consequence is easy to miss: an expired hold still occupies its slot until something removes it. Either a scheduled job deletes bookings whose expires_at has passed, or the availability check ignores them and the constraint is adjusted to match. The diagram did not reveal that; writing the constraint did. That is the right division of labour. The diagram gets the structure right, and the SQL forces the remaining details into the open.

In DiagramDock, each table in an ER diagram is written as text, one column per line with its type and any PK, FK, UNIQUE or NULL flags, so the diagram above is already close to a column list. Once the SQL exists, pasting it into "Visualise SQL schema" draws it back as tables (the SQL is parsed, not run), which is a quick way to check the schema you wrote still matches the one you designed.