Data dictionary examples

Complete data dictionary examples from realistic schemas — an e-commerce shop and a healthcare system — with the SQL each row derives from, and a free ErdDocs generator that produces the same document from your own schema.

What is a data dictionary?

A data dictionary is a document that describes every table and column in a database — name, data type, nullability, default value, keys and a human-written description — so that anyone joining the project can tell what the data means without reading the code. A complete entry has seven fields: Table, Column, Data type, Nullable, Default, Key and Description. Dictionaries for regulated domains — healthcare, finance — usually add an eighth: allowed values, the codes a column can hold and what each one means. That eighth field is where most of the real value hides, because a schema can declare a column’s type but never what its values mean to the business. Standards bodies formalize the same idea as a metadata registry (ISO/IEC 11179); in practice, a working dictionary is one row per column, filled from the schema rather than from memory.

Example 1: an e-commerce database

The orders table of a small shop — one row per column, exactly as a dictionary describes it:

#	Column	Data type	Nullable	Default	Key	Description
1	id	bigint	No		PK	Order number; customer-visible
2	customer_id	bigint	No		FK → customers.id	Owning customer
3	status	varchar(20)	No	'pending'		Lifecycle: pending → paid → shipped → done
4	total	numeric(10,2)	No			Grand total in the shop currency

Note the allowed values on status: a real dictionary spends its pages on what codes mean, not just on types. The full schema is on the e-commerce example page.

Example 2: a healthcare database

Healthcare dictionaries are the strictest kind — regulators read them. The patients table:

#	Column	Data type	Nullable	Key	Description
1	id	bigint	No	PK	Internal identifier; never shown to clinicians
2	mrn	varchar(20)	No	Unique	Medical record number — the working identifier
3	full_name	varchar(120)	No		Legal name as registered
4	born_on	date	No		Date of birth; age is computed, never stored
5	blood_type	varchar(3)	Yes		NULL until the first lab result arrives

The description column is where the dictionary earns its keep: "NULL until the first lab result arrives" is knowledge the DDL cannot carry. Full schema: hospital management system.

The SQL behind an example

A dictionary is not written next to the database — it is derived from it. Each DDL fact lands in a dictionary field: NOT NULL becomes Nullable: No, DEFAULT 'pending' becomes the Default cell, REFERENCES customers(id) becomes FK → customers.id, a CHECK (status IN (…)) becomes the allowed values, and COMMENT ON becomes the description. Everything except free-text prose fills itself — which is why generating beats typing.

More example schemas

Every schema on the examples page renders both an ER diagram and a data dictionary — school management, library, blog platform and more. Open one and switch to the Data Dictionary tab: ErdDocs writes it from the SQL on the page — types, defaults, keys and comments all come out of the DDL, never out of memory. For a schema you cannot dump, there is a CSV template with column-by-column guidance.

Generate one from your schema

Paste a pg_dump --schema-only /mysqldump --no-data dump, a Prisma schema, or framework models — the dictionary below is generated from the same shop SQL the first example describes. Parsing runs in your browser; the schema never leaves your device.

Your schema

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(120),
    created_at  timestamptz  NOT NULL DEFAULT now()
);
COMMENT ON COLUMN customers.email IS 'Login identity; unique across the system';

CREATE TABLE products (
    id     bigserial    PRIMARY KEY,
    sku    varchar(40)  NOT NULL UNIQUE,
    title  varchar(200) NOT NULL
);

CREATE TABLE orders (
    id           bigserial     PRIMARY KEY,
    customer_id  bigint        NOT NULL REFERENCES customers(id),
    status       varchar(20)   NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'paid', 'shipped', 'done')),
    total        numeric(10,2) NOT NULL
);
COMMENT ON COLUMN orders.status IS 'Lifecycle: pending -> paid -> shipped -> done';

CREATE TABLE order_items (
    order_id    bigint        NOT NULL REFERENCES orders(id),
    product_id  bigint        NOT NULL REFERENCES products(id),
    qty         int           NOT NULL,
    unit_price  numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
COMMENT ON TABLE order_items IS 'Line items; unit_price is frozen at purchase time';

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(120),
    created_at  timestamptz  NOT NULL DEFAULT now()
);
COMMENT ON COLUMN customers.email IS 'Login identity; unique across the system';

CREATE TABLE products (
    id     bigserial    PRIMARY KEY,
    sku    varchar(40)  NOT NULL UNIQUE,
    title  varchar(200) NOT NULL
);

CREATE TABLE orders (
    id           bigserial     PRIMARY KEY,
    customer_id  bigint        NOT NULL REFERENCES customers(id),
    status       varchar(20)   NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'paid', 'shipped', 'done')),
    total        numeric(10,2) NOT NULL
);
COMMENT ON COLUMN orders.status IS 'Lifecycle: pending -> paid -> shipped -> done';

CREATE TABLE order_items (
    order_id    bigint        NOT NULL REFERENCES orders(id),
    product_id  bigint        NOT NULL REFERENCES products(id),
    qty         int           NOT NULL,
    unit_price  numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
COMMENT ON TABLE order_items IS 'Line items; unit_price is frozen at purchase time';

Your diagram appears here.

FAQ

What is a data dictionary, in one sentence?

What does a data dictionary example include?

One row per column. The seven fields that cover nearly every real dictionary: Table, Column, Data type, Nullable, Default, Key and Description. Dictionaries for regulated domains often add allowed values (status codes with their meanings), which is where most of the value hides.

How is a data dictionary different from a data catalog?

Scale and audience. A data dictionary describes one database, column by column, and lives with the team that owns it — that is the document ErdDocs generates from a pasted schema. A data catalog indexes many data sources across a company and answers "where is this data at all?"; catalogs usually contain dictionaries, and a dictionary never contains a catalog, so the per-database document is the piece you need either way.

How do I create a data dictionary for my own database?

Three ways. The fastest is to generate it from the schema you already have: paste a SQL dump into the ErdDocs generator on this page, and every field except free-text descriptions fills itself, COMMENT ON descriptions included. The alternatives are a spreadsheet template filled in by hand, or a documentation suite that connects to the live database; for most teams neither is needed, because the schema dump is already on disk.

Data dictionary generator — the tool behind every example on this page.
Data dictionary template — a CSV to fill by hand, with guidance per column.
Database documentation tools — the landscape, from free generators to enterprise suites.