Database Schema Designer

Database Schema Designer

Visual ERD designer for database schemas. Create tables, define relationships, foreign keys. Export to SQL for MySQL, PostgreSQL, SQLite. Free online ERD tool

Designing a relational schema means making decisions you cannot easily change later: which fields are primary keys, what is the relationship between users and orders, where to denormalize for read speed. This designer is a visual ERD editor that produces CREATE TABLE statements for Postgres, MySQL, SQLite, and SQL Server — with foreign keys, indexes, and constraints. The output is real SQL you can run, not screenshot-only diagrams.

Schema design decisions ranked by impact

  • Primary key choice — auto-increment integer (simple, small), UUID (globally unique, larger, harder to index efficiently), or natural key (matches business identifier, brittle when business changes). Most modern applications: UUID v7 (time-ordered, indexable, globally unique).
  • Normalization level — 3NF for OLTP (insert/update workloads), star schema or denormalized for analytics. Wrong choice causes either insert anomalies or slow joins; correctible later but expensive.
  • Indexes — every B-tree index speeds reads and slows writes. Add the indexes your queries need; remove the ones you do not. EXPLAIN is the verification.
  • Foreign keys — referential integrity guaranteed by the database vs by the application. DB-enforced is safer but slower on bulk inserts. Most production systems: DB-enforced.
  • NULL vs NOT NULL — every column should be NOT NULL unless there is a specific reason for nullable. NULL semantics break aggregations, comparisons, and joins in subtle ways.
  • CHECK constraints — enforce invariants at the database. "age >= 0", "status IN ('active', 'inactive')". Less common than they should be.
  • Cascading deletes — ON DELETE CASCADE on FK. Useful for owned relationships (delete user → delete sessions); dangerous for shared (delete user → delete posts they wrote that other users reference).

Working example: an e-commerce schema

Input

A minimal e-commerce schema: users, products, orders

Output

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Notes: money in cents (integer), never floats — never. ON DELETE RESTRICT on user_id prevents deleting users with orders (legal/audit reasons). ON DELETE CASCADE on order_items.order_id removes line items when the order is deleted (owned relationship). Currency as CHAR(3) ISO 4217 code, not "USD"/varchar(10).

Migration patterns

  • Adding a column — generally safe. ALTER TABLE ADD COLUMN ... NULL works without locking on most databases. NOT NULL with default may require backfill.
  • Adding NOT NULL to existing column — three-step: (1) add as nullable, (2) backfill non-null values, (3) ALTER to NOT NULL. Single-statement attempt locks the table.
  • Renaming a column — single statement, fast, but breaking change for any consumer. Coordinate with deploys.
  • Dropping a column — irreversible (in production). First nullable + stop writing, then drop after a release cycle without consumers.
  • Adding a foreign key — ALTER TABLE ADD CONSTRAINT. Validates existing data; fails if inconsistent. Backfill orphans first.
  • Changing a column type — varies by DB. Postgres ALTER TYPE rewrites the column (slow on big tables, table-locking). Online migrations require shadow columns + dual-write + swap.

When to reach for this tool

  • You are starting a new project and want to sketch the schema visually before writing the SQL.
  • You inherited an undocumented database and want to reverse-engineer the relationships into an ERD.
  • You are explaining the schema to a non-database team member and want a visual diagram.
  • You are designing a feature that touches 5+ tables and want to verify foreign key constraints make sense.

What this tool will not do

  • It will not run the schema. Output is SQL; you run it against your real database (or in the SQL playground for testing).
  • It will not introspect existing databases. To reverse-engineer, you need a tool that connects to the live database (dbdiagram.io, DBeaver, SchemaSpy). This designer is for forward design.
  • It will not validate data quality. Constraints (NOT NULL, CHECK, FK) catch some problems; semantic issues (duplicate users by email, orphaned records that pre-date FK constraints) need data audits.

Frequently asked questions

Should I use UUID or auto-increment IDs?

For new systems in 2026: UUID v7 (time-ordered, globally unique, B-tree-friendly). For systems integrating with legacy: match the existing convention. Auto-increment ID is simpler but leaks information (you can guess "next user ID" sequentially) and complicates distributed inserts (need a central sequence).

Should I store money as INT or DECIMAL?

INT cents (or smaller unit) is simplest and avoids floating-point errors. DECIMAL(15, 2) is more flexible (multi-currency with different precisions). Never use FLOAT or DOUBLE — accumulating rounding errors break audits.

What is the difference between 3NF and denormalized?

3NF (third normal form) requires each non-key attribute to depend only on the primary key, not on other non-key attributes. Eliminates redundancy. Denormalization stores redundant data for read speed. OLTP systems: 3NF. Analytics warehouses: star schema (denormalized, optimized for aggregation).

When should I use a JSON column?

For data that has variable structure per row and is rarely queried by individual fields. User-specific preferences, audit log payloads, integration-specific metadata. For data you regularly filter or aggregate by, use a proper column — JSON-path queries are slower and less indexable than B-tree column queries.

How many indexes is too many?

Each index doubles the cost of inserts/updates roughly. For a write-heavy table, 3-5 indexes is reasonable. For read-heavy, 10+ may be justified. Run EXPLAIN ANALYZE on your slow queries; add indexes only for queries that need them.

Should I use ENUMs for status fields?

Postgres ENUMs are typed but hard to extend (adding a value requires ALTER TYPE). Most teams prefer TEXT with CHECK constraints — flexible and almost as fast. Reserve database-level ENUMs for truly fixed sets (currency codes, sex, country codes).

Related tools

SQL Playground

Run SQL queries in your browser with SQLite. Import CSV files, create tables, export results. Free online SQL editor and database sandbox

SQL Formatter & Beautifier

Format and beautify SQL queries with syntax highlighting. Support for MySQL, PostgreSQL, SQL Server. Free online SQL formatter and pretty printer

Data Converter

Convert data between JSON, CSV, YAML, XML, TOML formats online. Free data format transformer with syntax validation and pretty printing

JSON Schema Generator

Generate JSON Schema from example JSON data automatically. Create validation schemas for APIs. Free online JSON Schema builder for developers

Architecture Diagrammer

Create C4 model architecture diagrams. Design system context, container, component diagrams visually. Export to PNG/SVG. Free software architecture tool

Diagram & Flowchart Editor

Create flowcharts, UML diagrams, mind maps visually. Export to SVG/PNG. Free online diagram maker and flowchart creator

Last updated · E-Utils editorial team