Database Design for Developers

Twelve hands-on parts from an ER sketch to a production schema, on SQL Server and PostgreSQL side by side: keys, relationships, column types, constraints, normalisation, naming, indexes, soft delete and history, migrations, and search. Every script was run on both engines; every output shown is real.

12 parts · ~70 min of reading, in order, nothing assumed.

Start with Part 1

📥 The finished clinic schema for PostgreSQL (every part has its own script to download)📥 The finished clinic schema for SQL Server 🧭 The series this one continues: Conversation to System Design 📦 ClinicLive, the clinic whose tables start the series

Database Design for Developers · Part 1

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

Turn an ER diagram into real tables. Patients and appointments built on SQL Server and PostgreSQL side by side, with the first join and the first failed insert. Sep 26, 2026 6 min read

Database Design for Developers · Part 2

Primary Keys: Identity, GUID or Natural Key?

Which primary key should a table have? Identity, sequential GUID and random GUID measured on 100,000 rows, and why a phone number makes a poor key. Sep 26, 2026 6 min read

Database Design for Developers · Part 3

One-to-Many Relationships: Foreign Keys and ON DELETE

What ON DELETE should a foreign key use? Restrict, cascade and set null shown on real tables, plus the index every foreign key column needs. Sep 26, 2026 5 min read

Database Design for Developers · Part 4

Many-to-Many Relationships: Junction Tables Done Properly

How to model many-to-many in SQL. Doctors and specialties through a junction table, composite key or surrogate id, and the query that reads it back. Sep 26, 2026 4 min read

Database Design for Developers · Part 5

Choosing Column Types: Strings, Money, Dates, Booleans and Status

Which data type for money, dates or phone numbers? Real demonstrations of what goes wrong with float, local times and varchar limits, on both engines. Sep 26, 2026 7 min read

Database Design for Developers · Part 6

Constraints: Where Business Rules Belong

Should validation live in the database or the app? NOT NULL, UNIQUE, CHECK, DEFAULT and a filtered unique index, each shown passing and failing. Sep 26, 2026 6 min read

Database Design for Developers · Part 7

Normalization With a Real Example: From Spreadsheet to 3NF

What normalization means, on a real table. A flat visits spreadsheet shows its three anomalies, then gets repaired to first, second and third normal form. Sep 26, 2026 5 min read

Database Design for Developers · Part 8

Naming Conventions: Singular or Plural, snake_case or PascalCase

Database naming conventions that survive two engines. Case folding in PostgreSQL, collation in SQL Server, reserved words, and a rule list to keep. Sep 26, 2026 5 min read

Database Design for Developers · Part 9

Indexes Explained With Query Plans: Which Columns to Index

Which columns should you index? Three queries on 200,000 appointments, before and after, with the plans and timings from SQL Server and PostgreSQL. Sep 26, 2026 6 min read

Database Design for Developers · Part 10

Soft Delete, Audit Columns and History Tables

How to implement soft delete and an audit trail. A deleted_at column with a partial unique index, timestamps by trigger, temporal tables versus a history table. Sep 26, 2026 6 min read

Database Design for Developers · Part 11

Schema Migrations: Add, Rename and Backfill Without Downtime

How to change a live table safely. Adding columns, renaming in steps, backfilling in batches and widening a type, timed on 200,000 rows on both engines. Sep 26, 2026 7 min read

Database Design for Developers · Part 12

Search, Reporting Views and the Finished Clinic Schema

How to add search to a database. LIKE against full text on both engines, a reporting view, the finished clinic schema to download, and a twelve-rule checklist. Sep 26, 2026 7 min read