Data dictionary template

A data dictionary is one row per column of your database: name, type, nullability, default, keys, description. Below is a template with the seven columns that cover nearly every real dictionary — download it as CSV (opens in Excel and Google Sheets), or copy the table straight into Word. And if the database already exists, ErdDocs fills every one of those rows from a SQL dump — the generator below does it as you paste.

The template

Table	Column	Data type	Nullable	Default	Key	Description
customers	id	bigint	No		PK	Internal identifier; never shown to users
customers	email	varchar(255)	No		Unique	Login identity; unique across the system
customers	full_name	varchar(120)	Yes			Display name as entered at signup
orders	id	bigint	No		PK	Order number; customer-visible
orders	customer_id	bigint	No		FK → customers.id	Owning customer
orders	status	varchar(20)	No	'pending'		Lifecycle: pending → paid → shipped → done

The rows are examples — replace them with your tables. data-dictionary-template.csv

How to fill each column

Data type — as declared, with its size: varchar(255), not "text-ish". Auditors compare this against the live database.
Nullable — a plain Yes/No. The most common dictionary bug is writing "No" for a column the database happily stores NULL in; take it from the DDL, not from memory.
Default — literals quoted ('pending'), expressions as written (now()). Leave truly empty when there is none.
Key — PK, Unique, or FK with its target: FK → customers.id. A key without a target is a trivia answer, not documentation.
Description — what the name cannot say: units, lifecycle, ownership, what NULL means. If it only repeats the column name, leave it empty — noise is worse than absence.

Or skip the typing

If the database exists, the dictionary should come from it, not from memory. Paste a pg_dump --schema-only /mysqldump --no-data dump or a Prisma schema — every template column fills itself, descriptions included when the schema carries comments. The example is the same shop the template rows describe:

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',
    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',
    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 columns should a data dictionary template have?

Seven cover nearly every real dictionary: Table, Column, Data type, Nullable, Default, Key (primary/foreign/unique) and Description. Teams sometimes add Source (for warehouse pipelines), PII flag (for privacy audits) or Example value — add them only when someone will actually maintain them.

Excel or Word — which format is better for a data dictionary?

Excel (or any spreadsheet) while the dictionary is being written: one row per column, filterable, sortable. Word or PDF when it is being handed over: auditors and clients expect a document. The CSV template here opens in Excel; the generated dictionary prints straight to PDF.

How is the Description column different from the column name?

A description says what the name cannot: units ('cents, not dollars'), lifecycle ('pending → paid → shipped'), ownership ('set by the billing job, never by hand'), and edge cases ('NULL means the customer wrote it'). If the description only repeats the name, delete it — empty is more honest than noise.

Can the template be filled automatically?

ErdDocs fills it: paste a SQL dump (pg_dump --schema-only, mysqldump --no-data) or a Prisma schema, and every template column — including descriptions, when the schema carries comments — is filled from the DDL. Hand-typing is what is left for a database you cannot export; if you can run pg_dump or mysqldump, ErdDocs has already written those rows for you.

Data dictionary generator — fill the template from a SQL dump.
Data dictionary examples — what filled-in dictionaries look like, schema by schema.
MySQL ·PostgreSQL — dialect-specific pages.