From ER Diagram to CREATE TABLE: Turning Entities into Real Tables

The Conversation to System Design series ended with an ER diagram: boxes for patients and appointments, a line between them, a list of attributes in each box. This series starts where that one stopped. Twelve parts, and in each one the diagram gets a little more real, on SQL Server and PostgreSQL side by side, with every script actually run and every output shown as the engine printed it. Part 1 turns two boxes into two tables.

The two boxes

The running example is ClinicLive, the clinic app built across four seasons on this site. Its Patient and Appointment classes are the two boxes from the diagram, and the table below is the whole translation: each attribute, the SQL Server column, the PostgreSQL column.

Two habits are worth forming on day one. Table and column names are lower-case with underscores, the same on both engines, so that nothing depends on how an engine folds case (Part 8 shows what happens when it does). And every constraint gets a name you chose, because the name is what the error message will show you later.

patients

SQL Server first. Section 2 of the script:

CREATE TABLE dbo.patients (
    id          BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT pk_patients PRIMARY KEY,
    full_name   NVARCHAR(200) NOT NULL,
    phone       NVARCHAR(30)  NOT NULL,
    email       NVARCHAR(200) NULL,
    created_at  DATETIME2(3)  NOT NULL CONSTRAINT df_patients_created_at DEFAULT SYSUTCDATETIME()
);
-- One patient per phone number (ClinicLive looks patients up by phone).
CREATE UNIQUE INDEX ix_patients_phone ON dbo.patients (phone);

And the same table in PostgreSQL:

CREATE TABLE patients (
    id          bigint GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_patients PRIMARY KEY,
    full_name   varchar(200) NOT NULL,
    phone       varchar(30)  NOT NULL,
    email       varchar(200),
    created_at  timestamptz  NOT NULL DEFAULT now()
);
-- One patient per phone number (ClinicLive looks patients up by phone).
CREATE UNIQUE INDEX ix_patients_phone ON patients (phone);

Read them as one design with two spellings. The id is a 64-bit integer the database hands out itself; IDENTITY(1,1) and GENERATED BY DEFAULT AS IDENTITY are the two ways of saying so, and bigint rather than int because changing that later costs a table rewrite (Part 11 measures it). Strings carry a length that is a business rule, not a storage decision; SQL Server's N prefix means Unicode, which PostgreSQL strings are anyway. The creation time is a moment in UTC, filled by the database when the insert leaves it out; SQL Server stores that default as a named constraint, PostgreSQL as a property of the column.

appointments: the line becomes a column

The diagram's line from patient to appointment becomes one column and one rule. The column, patient_id, holds a patient's id; the rule, the foreign key, says the database will refuse any value that is not in patients.id. Section 3 in PostgreSQL:

CREATE TABLE appointments (
    id                 bigint GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_appointments PRIMARY KEY,
    patient_id         bigint       NOT NULL
        CONSTRAINT fk_appointments_patients_patient_id REFERENCES patients (id),
    starts_at          timestamptz  NOT NULL,
    status             varchar(20)  NOT NULL,
    confirmation_code  varchar(6)   NOT NULL,
    created_at         timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT ck_appointments_status
        CHECK (status IN ('Booked', 'CheckedIn', 'InProgress', 'Done', 'Cancelled', 'NoShow'))
);
CREATE UNIQUE INDEX ix_appointments_confirmation_code ON appointments (confirmation_code);

The SQL Server version differs only in the spellings you already saw: dbo., BIGINT IDENTITY(1,1), NVARCHAR, DATETIME2(3), and N'Booked' in the CHECK list. The status is stored as text and limited to the six words the app knows; Part 5 weighs that against a lookup table and PostgreSQL's enum type.

Section 4 adds the indexes ClinicLive really has on this table, and one of them carries the clinic's central rule: one active appointment per time slot. It is a unique index with a filter, so cancelled and no-show rows do not block the slot:

CREATE UNIQUE INDEX ix_appointments_slot_active_unique
    ON appointments (starts_at)
    WHERE status NOT IN ('Cancelled', 'NoShow');
-- An index on the foreign key column, and one on starts_at for day-range pages.
CREATE INDEX ix_appointments_patient_id ON appointments (patient_id);
CREATE INDEX ix_appointments_starts_at_all ON appointments (starts_at);

That is ClinicLive's filter word for word, and SQL Server refuses it: a filtered index there does not accept NOT IN (Msg 102, Incorrect syntax near 'NOT'), so the SQL Server script spells the same rule as the list of the four active statuses. It also needs QUOTED_IDENTIFIER on, which sqlcmd turns off by default; that is what the -I in the run command is for, and without it you get Msg 1934.

Rows in, rows out

Section 5 inserts three patients and four appointments without ids and without creation times, and the database fills both. Section 6a joins the two tables, which is the first thing the diagram's line was drawn for:

SELECT a.id, p.full_name, a.starts_at, a.status
FROM appointments AS a
JOIN patients     AS p ON p.id = a.patient_id
ORDER BY a.starts_at;

 id |  full_name   |       starts_at        |  status
----+--------------+------------------------+-----------
  1 | Maria Garcia | 2026-09-22 09:00:00+00 | Done
  4 | Aisha Khan   | 2026-09-24 11:00:00+00 | Cancelled
  3 | David Chen   | 2026-09-28 09:15:00+00 | Booked
  2 | Maria Garcia | 2026-09-29 10:30:00+00 | Booked
(4 rows)

Section 6b asks the question the front desk asks: each patient's next appointment. A LEFT JOIN keeps the patients who have none, and the join condition, not a WHERE, carries the "from now on, still active" rules, otherwise those patients would vanish. "Now" is fixed at 26 September 08:00 UTC so the result is the same whenever you run it:

SELECT p.id, p.full_name, MIN(a.starts_at) AS next_appointment
FROM patients AS p
LEFT JOIN appointments AS a
       ON a.patient_id = p.id
      AND a.starts_at >= '2026-09-26 08:00:00+00'
      AND a.status IN ('Booked', 'CheckedIn')
GROUP BY p.id, p.full_name
ORDER BY p.id;

 id |  full_name   |    next_appointment
----+--------------+------------------------
  1 | Maria Garcia | 2026-09-29 10:30:00+00
  2 | David Chen   | 2026-09-28 09:15:00+00
  3 | Aisha Khan   |
(3 rows)

SQL Server returns the same three rows; it prints the times as 2026-09-29 10:30:00.000 without an offset, because DATETIME2 stores no zone and UTC is a convention the app keeps, and it prints NULL where psql leaves the cell empty.

The first refusal

Section 7 books an appointment for patient 999, who does not exist. Both engines refuse it, and each message is worth reading once, because you will read it many times:

Msg 547, Level 16, State 1, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_appointments_patients_patient_id". The conflict occurred in database "dd_p01", table "dbo.patients", column 'id'.
The statement has been terminated.

ERROR:  insert or update on table "appointments" violates foreign key constraint "fk_appointments_patients_patient_id"
DETAIL:  Key (patient_id)=(999) is not present in table "patients".

The SQL Server line has its server name trimmed; nothing else is changed. Note what each engine tells you: SQL Server names the parent table and column but not the value, PostgreSQL names the child table and prints the bad value. Both name the constraint, which is why section 3 named it. Nothing was inserted, section 7b counts four rows, and then section 7c shows a detail that surprises people: the next successful insert got id 6, not 5. A failed insert still consumes an identity value, on both engines, and identity values are never meant to be gap-free.

What the database knows

Section 8 asks the engine to describe the table. The standard information_schema.columns view exists on both, with the same query text, and the answers show the type names each engine uses for the same design:

    column_name    |        data_type         | character_maximum_length | is_nullable | column_default
-------------------+--------------------------+--------------------------+-------------+----------------
 id                | bigint                   |                          | NO          |
 patient_id        | bigint                   |                          | NO          |
 starts_at         | timestamp with time zone |                          | NO          |
 status            | character varying        |                       20 | NO          |
 confirmation_code | character varying        |                        6 | NO          |
 created_at        | timestamp with time zone |                          | NO          | now()

column_name data_type character_maximum_length is_nullable column_default
id bigint NULL NO NULL
patient_id bigint NULL NO NULL
starts_at datetime2 NULL NO NULL
status nvarchar 20 NO NULL
confirmation_code nvarchar 6 NO NULL
created_at datetime2 NULL NO (sysutcdatetime())

The engine's own tools go further: EXEC sp_help 'dbo.appointments' lists the identity, the indexes and the constraints, and psql's \d appointments prints the same plus the filter of the filtered index. Both rewrite your CHECK constraint into their own form when they store it; SQL Server keeps it as a chain of ORs, PostgreSQL as = ANY (ARRAY[...]). The rule is the same.

Frequently asked

How do I convert an ER diagram into SQL tables?

Each entity becomes a table, each attribute a column with a type and a NOT NULL decision, and each one-to-many line a foreign key column on the many side with a REFERENCES constraint. Name every constraint yourself, so errors name the rule that was broken.

Why does the SQL Server script use NVARCHAR and the PostgreSQL script varchar?

SQL Server has two string families and only the N-prefixed one stores every language. PostgreSQL databases are UTF-8 throughout, so varchar and text hold everything. The length in both is a business rule, not a storage optimisation.

Why did the id jump from 4 to 6 after a failed insert?

Identity values are taken from a counter before the row is checked, and a failed insert does not give its value back on either engine. Gaps are normal; an identity column promises uniqueness, not a gap-free sequence.

Next: Part 2, primary keys, where three versions of the patients table get 100,000 rows each and the sizes are measured. The diagram this part builds on is in Part 6 of the 2024 series.