Skip to content
MarkdownTables
Developer Template6 columns · 6 sample rows· Updated

Relational Database Table DDL Schema Markdown Table Template

A schema table is a data dictionary for one database table: every column with its SQL type, its key role, whether it may be NULL, what the database fills in when an INSERT leaves it out, and what the value means in business terms. The DDL already says most of this to the database. The table exists to say it to people, and to add the meaning that CREATE TABLE cannot express.

Edit This Template

Edit cells, add rows, or sort — the Markdown updates live
Row
1
`id`
UUID
PK
gen_random_uuid()
2
organization_id
UUID
FK
References organizations(id) ON DELETE CASCADE
3
email
VARCHAR(255)
UNIQUE
4
role
VARCHAR(50)
'member'
Permission tier (admin, member, guest)
5
is_verified
BOOLEAN
false
6
created_at
TIMESTAMPTZ
INDEX
NOW()
Output
| Column Name       | Data Type      |   Key    | Nullable |       Default Value | Description                                      |
| :---------------- | :------------- | :------: | :------: | ------------------: | :----------------------------------------------- |
| **`id`**          | `UUID`         |   `PK`   |    No    | `gen_random_uuid()` | Globally unique row identifier                   |
| `organization_id` | `UUID`         |   `FK`   |    No    |                   — | References `organizations(id)` ON DELETE CASCADE |
| `email`           | `VARCHAR(255)` | `UNIQUE` |    No    |                   — | Normalized lowercase user email address          |
| `role`            | `VARCHAR(50)`  |    —     |    No    |          `'member'` | Permission tier (`admin`, `member`, `guest`)     |
| `is_verified`     | `BOOLEAN`      |    —     |    No    |             `false` | True once user completes email confirmation      |
| `created_at`      | `TIMESTAMPTZ`  | `INDEX`  |    No    |             `NOW()` | UTC creation timestamp                           |
6 rows × 6 columns
Rendered Preview
Column NameData TypeKeyNullableDefault ValueDescription
`id`UUIDPKNogen_random_uuid()Globally unique row identifier
organization_idUUIDFKNo—References organizations(id) ON DELETE CASCADE
emailVARCHAR(255)UNIQUENo—Normalized lowercase user email address
roleVARCHAR(50)—No'member'Permission tier (admin, member, guest)
is_verifiedBOOLEAN—NofalseTrue once user completes email confirmation
created_atTIMESTAMPTZINDEXNoNOW()UTC creation timestamp

When to Use This Table

Its readers make real decisions from it. A backend developer writing a query wants to know whether organization_id is indexed and what happens to a user row when its organisation is deleted. An analyst writing a dashboard needs to know whether created_at is an instant or a local wall-clock time. A reviewer looking at a migration compares the proposed table against this one to spot a new nullable column that should have been NOT NULL. A data engineer building a pipeline needs exact types so the destination warehouse does not truncate anything.

What separates a good schema table from a bad one is that it is generated from, or checked against, the live database, and that it records things the type name hides: units, allowed values, ON DELETE behaviour, and which engine the types belong to. TIMESTAMPTZ and gen_random_uuid() are PostgreSQL; the MySQL version of this table looks different. A table that mixes dialects, or quotes string defaults with double quotes, will mislead anyone who copies from it into a migration.

What Goes in Each Column

ColumnAlignmentWhat to put here
Column Nameleft :---The physical column name in backticks, in the database’s own casing, usually snake_case. The template bolds the primary key so it stands out. List columns in ordinal_position order, which is what SELECT * returns and what \d in psql prints.
Data Typeleft :---The type as the engine reports it, with length or precision: VARCHAR(255), NUMERIC(12,2), TIMESTAMPTZ. Name the dialect somewhere on the page, because BOOLEAN is a real type in PostgreSQL but an alias for TINYINT(1) in MySQL, and SQLite only has storage classes.
Keycenter :---:Short centered markers: PK, FK, UNIQUE, or INDEX. A column can carry several (FK, INDEX). Strictly, a plain index is not a key constraint, but listing it here answers the question readers actually ask — will filtering on this column be fast? Use — instead of “None”.
Nullablecenter :---:Yes or No, centered, matching the is_nullable value from information_schema. Primary key columns are always No. When a column is nullable, say in Description what NULL means for it — “not yet verified” and “unknown” call for different handling in queries.
Default Valueright ---:The DEFAULT expression exactly as the database stores it, in backticks. String literals take single quotes ('member'); double quotes mean an identifier in standard SQL. Functions such as now() run at insert time. Write — when there is no default, which is not the same as a NULL default.
Descriptionleft :---The business meaning, plus whatever the type hides: units (cents, seconds), allowed values and how they are enforced, the referenced table with its ON DELETE rule, and whether the value is set by the application or a trigger. One or two sentences per row.

How to Fill It In

  1. 1Pull column metadata from the live database, not from memory: in PostgreSQL run SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position;, or SHOW FULL COLUMNS FROM users; in MySQL. Paste the result into the SQL to Markdown converter.
  2. 2Add constraints and indexes. \d users in psql lists the primary key, unique constraints, indexes, and foreign keys with their ON DELETE actions in one screen.
  3. 3Write each description from the code that reads and writes the column: the ORM model, validators, and any triggers. This is where units, enum values, and the meaning of NULL come from.
  4. 4Diff the result against your migration files or ORM schema. Any column that differs is drift between environments, and it should be fixed rather than documented.
  5. 5Regenerate the table in the same pull request as every migration that touches the table, so reviewers see the schema change and the documentation change together.

Ready-to-Paste Variations

MySQL 8 version of the same table

The template rows rewritten for MySQL 8.0.13 or later, which allows expression defaults in parentheses. Note how UUID and boolean storage differ.

| Column | Type | Key | Null | Default |
| :--- | :--- | :---: | :---: | :--- |
| **`id`** | `BINARY(16)` | `PK` | No | `(UUID_TO_BIN(UUID()))` |
| `organization_id` | `BINARY(16)` | `FK` | No | — |
| `email` | `VARCHAR(255)` | `UNIQUE` | No | — |
| `is_verified` | `TINYINT(1)` | — | No | `0` |
| `created_at` | `DATETIME(6)` | `INDEX` | No | `CURRENT_TIMESTAMP(6)` |

Foreign key map

A second table for relationships, which a per-column table cannot show well. Useful when reviewing delete behaviour across a whole schema.

| Column | References | On delete | Indexed |
| :--- | :--- | :--- | :---: |
| `users.organization_id` | `organizations.id` | `CASCADE` | Yes |
| `projects.owner_id` | `users.id` | `SET NULL` | Yes |
| `invoices.organization_id` | `organizations.id` | `RESTRICT` | Yes |
| `audit_log.actor_id` | `users.id` | `NO ACTION` | No |

Compact data dictionary

For a README or analytics wiki where readers care about meaning more than DDL details. Constraints are folded into the type.

| Column | Type | Meaning |
| :--- | :--- | :--- |
| `id` | `uuid`, PK | Stable user identifier, safe to expose in URLs |
| `email` | `varchar(255)`, unique | Lowercased before insert |
| `role` | `varchar(50)` | `admin`, `member`, or `guest`; enforced by a `CHECK` constraint |
| `created_at` | `timestamptz` | Instant of sign-up; displayed in the reader’s time zone |

Common Mistakes

  • Writing a string default in double quotes, such as "member".

    In PostgreSQL and standard SQL, double quotes delimit identifiers, so DEFAULT "member" refers to a column and fails. Document the default as the database stores it: 'member'::character varying in PostgreSQL, or simply 'member'.

  • Assuming every FK column is indexed.

    MySQL’s InnoDB creates an index for a foreign key automatically; PostgreSQL does not. Without one, deleting a parent row scans the child table. Add the index and mark it in Key.

  • Listing allowed values in Description that nothing enforces.

    If role is meant to be admin, member, or guest, add a CHECK constraint, an enum type, or a lookup table with a foreign key, and say which one is in place. Otherwise the database will accept any string.

  • Calling email unique when the constraint is case-sensitive.

    A PostgreSQL UNIQUE on varchar treats Ann@x.io and ann@x.io as different. Use a unique index on lower(email) or the citext type. MySQL’s default _ci collations already compare case-insensitively.

  • Reaching for VARCHAR(255) everywhere out of habit.

    In PostgreSQL, varchar(n) and text perform the same; the length is only a constraint. Choose limits that match the domain, such as 254 for email addresses, the longest address SMTP can deliver, and document why.

Related Markdown Table Templates

Browse all 50 templates
Developer

GraphQL Schema Types & Query Operations

Document GraphQL Queries, Mutations, Subscription types, input parameters, return payloads, authentication requirements, and deprecations.

Open template
Data & AI

Cross-Language Data Type Mapping

Map fundamental data types across TypeScript, Python, SQL (PostgreSQL), Rust, and JSON standards.

Open template
Developer

Project Architecture & Tech Stack

List technologies, libraries, packages, and cloud services powering your application by architectural tier.

Open template
FAQ

Relational Database Table DDL Schema: Frequently Asked Questions

Should I use TIMESTAMP or TIMESTAMPTZ in PostgreSQL?

Use TIMESTAMPTZ for moments when something happened. It stores an absolute instant and converts on input and output using the session time zone. Plain TIMESTAMP stores a wall-clock reading with no zone, which suits things like a store’s opening hour, not event times.

UUID or BIGINT for primary keys?

BIGINT GENERATED ALWAYS AS IDENTITY is compact and fast to index. UUIDs can be created by clients and do not reveal row counts. Random version 4 UUIDs scatter inserts across the index; time-ordered version 7 UUIDs avoid that, and PostgreSQL 18 generates them with uuidv7().

Where do composite indexes go?

Not in this table, because a per-column layout cannot show column order within an index. List them below it in a small table of index name, columns in order, and the query each one serves, so unused indexes are easy to spot.

Does now() as a default give each row a different time?

In PostgreSQL, now() returns the start time of the current transaction, so every row inserted in one transaction gets the same value. Use clock_timestamp() if you need the actual time of each statement.