What is a database schema diagram?
A database schema diagram is a visual representation of a database's structure — its tables, columns, data types, primary keys, foreign keys, indexes, and constraints. Unlike an Entity-Relationship Diagram (ERD), which models domain concepts at a conceptual level, a schema diagram represents the physical or logical implementation as it exists in SQL DDL. It is the answer to "what is actually in the database?"
Database schema diagrams are used during database design, code review, onboarding new developers, debugging query performance issues, documenting data models for compliance, and planning migrations. A well-drawn schema diagram makes foreign key relationships, normalization decisions, and indexing strategies immediately visible — turning abstract SQL into a readable map of your data architecture.
Elements of a database schema diagram
| Element | Notation | Purpose |
|---|---|---|
| Table | Rectangle with header row (table name) | Represents a database table. The header shows the table name; rows below list columns. |
| Column | Row with name, data type, and nullability | Each column shows: name, data type (VARCHAR(255), INTEGER, TIMESTAMP), and whether it is NOT NULL. |
| Primary key (PK) | Key icon or "PK" label; bold or underlined column | Uniquely identifies each row. May be a single column (id SERIAL) or composite (order_id, product_id). |
| Foreign key (FK) | Arrow from FK column to referenced PK | Enforces referential integrity between tables. Arrow direction shows the dependency: child table → parent table. |
| Unique constraint | "UQ" label or distinct line style | Enforces uniqueness on one or more columns without making them the primary key (e.g., users.email UNIQUE). |
| Index | Separate section within table box labeled "IDX" | Shows non-primary indexes: name, covered columns, type (BTREE, GIN, HASH), and whether unique or partial. |
| Relationship cardinality | Crow's foot notation on FK arrows | Shows one-to-one, one-to-many, or many-to-many relationships. Many-to-many uses a junction table. |
Example: e-commerce schema diagram
Use this prompt in flow-chart.io to generate an e-commerce database schema diagram:
Database schema diagram for an e-commerce application. Tables: users (id SERIAL PK, email VARCHAR UNIQUE NOT NULL, created_at TIMESTAMP), products (id SERIAL PK, name VARCHAR NOT NULL, price DECIMAL(10,2), stock_quantity INTEGER, category_id FK), categories (id SERIAL PK, name VARCHAR UNIQUE NOT NULL), orders (id SERIAL PK, user_id FK → users.id, status VARCHAR, total DECIMAL(10,2), created_at TIMESTAMP), order_items (order_id FK → orders.id, product_id FK → products.id, quantity INTEGER, unit_price DECIMAL — composite PK on order_id + product_id). Show indexes: idx_orders_user on orders(user_id), idx_products_category on products(category_id). Use crow's foot notation for relationships.
Schema diagram vs. ERD: when to use each
| Dimension | Schema Diagram | ERD |
|---|---|---|
| Level | Physical / logical implementation | Conceptual domain model |
| When used | Code review, debugging, documentation | Design phase before implementation |
| Shows | Exact table names, column types, constraints | Entities, attributes, relationships (abstract) |
| Notation | Implementation-specific (SQL types, DDL constraints) | Chen, crow's foot, or UML notation |
| Audience | Developers, DBAs, auditors | Business analysts, architects, stakeholders |
| Tool | Reverse-engineered from live DB or DDL | Drawn from domain knowledge |
Database schema diagram best practices
- Show every column with its exact data type and nullability — ambiguous columns are the primary source of schema misunderstandings.
- Use crow's foot notation on FK arrows to communicate cardinality (one-to-many, one-to-one) at a glance.
- Group tables by functional domain — user tables, order tables, product tables — and use color coding or swim lanes per group.
- Show junction tables explicitly for many-to-many relationships — they are first-class tables with their own FKs and composite PK.
- Include non-primary indexes in the diagram, especially composite indexes — they reveal query optimization decisions.
- Mark CHECK constraints and DEFAULT values on columns where they encode business rules (e.g., status IN ('pending', 'shipped', 'cancelled')).
- Add a diagram version and last-modified date — schemas evolve and stale diagrams cause expensive misunderstandings.
Frequently asked questions
- What is the difference between a database schema diagram and an ERD?
- An ERD models the conceptual domain — entities, attributes, and relationships at an abstract level. A database schema diagram represents the physical implementation — actual table names, column data types, constraints, PKs, FKs, and indexes as they exist in SQL. An ERD answers "how should we model this domain?" A schema diagram answers "what is in the database right now?"
- What should a database schema diagram include?
- A complete database schema diagram includes: all tables with exact names, all columns with data type and nullability, primary keys (PK), foreign keys (FK) with arrows to referenced tables, unique constraints, indexes (especially composite), and check constraints where they encode business rules. Show cardinality on relationships using crow's foot notation.
- How do I show database normalization in a schema diagram?
- Normalization is visible in the diagram's structure: 1NF shows atomic columns (no comma-separated lists). 2NF shows no partial dependencies — foreign keys point to the correct table. 3NF shows no transitive dependencies. Denormalized tables appear as wide tables with many columns; normalized schemas show more tables with cleaner FK relationships between them.
- What is the difference between MySQL and PostgreSQL schema diagrams?
- The visual structure is the same — tables, columns, PKs, FKs, and indexes look identical. Differences appear in data types: PostgreSQL supports JSONB, ARRAY, ENUM, and table inheritance. MySQL uses ENGINE=InnoDB annotations and different integer types. PostgreSQL schemas are namespace-qualified (schema.table). flow-chart.io generates diagrams compatible with both engines.
- How do I reverse-engineer a schema diagram from an existing database?
- Export the DDL (CREATE TABLE statements) using pg_dump, mysqldump, or your database IDE's export function. Paste the DDL into flow-chart.io's AI prompt with an instruction to generate a schema diagram. For large schemas, break them into functional groups (users and auth, orders and payments, products and inventory) and generate one diagram per domain.
- How do I show indexes in a database schema diagram?
- Show indexes as a separate section within the table box, below the column list, labeled "Indexes" or "IDX." List each index with its name, covered columns, and type (BTREE, GIN for PostgreSQL full-text). Mark composite indexes with all columns in order. Unique indexes get a "U" marker. The primary key index is implied by the PK marker and need not be listed separately.
- What export formats are available for database schema diagrams?
- flow-chart.io exports database schema diagrams as SVG (for technical documentation in Confluence, Notion, or GitHub wikis), PNG at 2× and 4× resolution (for slides and design docs), PDF (for data architecture reviews), and JSON (re-importable scene graph for future editing). All exports are derived from the editable scene graph — not screen captures.