A table specification has no official format. What it does have is a set of fields every reviewer looks for — column names (physical and logical), data type and length, nullability, default value, keys and constraints, and a description. Cover those and no one will call the document incomplete.
This article lays out those fields and builds a template you can copy, with worked examples. Then we'll look at what reviewers most often flag, and finally at how to stop writing the document by hand altogether.
What belongs in the specification
A specification has two halves: a summary of the table itself, and the definition of every column.
The table summary carries the physical name (members), the logical name (Member), a description, and sometimes the character set. Many templates also include author and last-modified columns. I'll come back to those — I have rarely seen them stay accurate.
Column definitions are the substance. What reviewers actually read:
- Column name, physical and logical
- Data type with length —
VARCHAR(255), not just "string" - Nullability
- Default value
- Keys and constraints — PK, FK, UNIQUE, indexed
- Description — one line on why the column exists
Each field carries more meaning than it looks. Nullability isn't just a technical attribute — it's a business rule on record. "Can a member exist without a phone number?" The answer lives in that column. The default value shows what the database does when the application stays silent; an empty cell reads as "the app always fills this in." For the key column, settle on short codes — PK, FK, UK, IX — and the table stays scannable.
Add an index list and a foreign key list (which table and column each FK references, and what happens on delete) as table-level appendices, and the document is complete. We'll see both in the template below. How those FK relationships read as diagram symbols is covered in the ERD notation guide.
A template you can copy
Let's fill the format with the forum database we designed in How to Draw an ERD. First the simplest table, members.
Table: members (Member): registered service accounts
| # | Column | Logical name | Type | Null | Default | Key | Description |
|---|---|---|---|---|---|---|---|
| 1 | id | Member ID | BIGINT | N | AUTO_INCREMENT | PK | Surrogate key |
| 2 | VARCHAR(255) | N | — | UK | Login account, can change | ||
| 3 | nickname | Nickname | VARCHAR(50) | N | — | — | Shown in the UI |
| 4 | created_at | Signup time | DATETIME | N | CURRENT_TIMESTAMP | — | DB records it if the app forgets |
A table with foreign keys needs one more piece of information per FK column. Here's posts in the same format.
Table: posts (Post): articles written by members in a category
| # | Column | Logical name | Type | Null | Default | Key | Description |
|---|---|---|---|---|---|---|---|
| 1 | id | Post ID | BIGINT | N | AUTO_INCREMENT | PK | Surrogate key |
| 2 | member_id | Author | BIGINT | N | — | FK | References members.id |
| 3 | category_id | Category | INT | N | — | FK | References categories.id |
| 4 | title | Title | VARCHAR(200) | N | — | — | |
| 5 | content | Body | MEDIUMTEXT | N | — | — | Sized for long posts |
| 6 | created_at | Created time | DATETIME | N | CURRENT_TIMESTAMP | — |
Below each table go the two appendices. This is where reviewers check relationships and performance.
Foreign keys
| Name | Column | References | On delete |
|---|---|---|---|
| fk_posts_member | member_id | members.id | RESTRICT |
| fk_posts_category | category_id | categories.id | RESTRICT |
Indexes
| Name | Columns | Type |
|---|---|---|
| pk_posts | id | PK |
| uq_members_email | UNIQUE |
Paste these into a spreadsheet and you have a template. One sheet per table, plus a cover sheet with the table list and an as-of date — which snapshot of the schema this document describes. That structure survives review anywhere.
One tip: an astonishing number of specifications ship with the logical name and description columns empty. Physical names and data types can be pulled from the database any time you want. "Why does this column exist" lives only in the document. That column is the reason you are writing the specification at all.
Five things reviewers flag most often
Knowing what the person receiving the document looks for lets you avoid the flags before they're raised. In rough order of frequency:
1. Logical names that don't match the screens. The UI says "Handle," the spec says "Nickname" — the reviewer's first question becomes "which one is right?" Logical names should follow the vocabulary of the specs and screens.
2. Types that don't match the data. The document says VARCHAR(50), the table contains 60-character values. It's the fingerprint of a document written first and a schema changed later, and it puts the whole document under suspicion.
3. "FK" with no target. A bare FK mark says nothing about which table and column it references, or what happens when the parent row is deleted. The reference target and the delete rule are part of the FK information.
4. No as-of date. Without one, the document can't answer "is this what the database looks like now?" One line on the cover sheet fixes it, and it's missing more often than you'd expect.
5. A document that disagrees with the live database. The most damaging flag, and the most common. It's a structural problem of hand maintenance — the next section — and personal diligence rarely fixes it.
Of the five, numbers 2, 3 and 5 disappear entirely with the generation approach we'll get to. A document produced from the schema has no way to disagree with it.
Why hand-maintained specs drift
Writing the document once is not the hard part. Everything after that is.
Schemas change for as long as development continues. Columns get added, types widen, indexes appear. Very few teams update the spreadsheet every time. Within a few weeks the specification and the database are telling different stories, and once someone catches them disagreeing, nobody opens the specification again.
File management fails the same way. spec_final.xlsx, spec_final_v2.xlsx, spec_final_REAL.xlsx — the joke is old because it keeps happening, and a team that cannot say which file is current is in the same position as a team whose document is wrong.
On contract work the stakes go up. The specification is a deliverable, so it gets reviewed — and reviewers read documents before they read code. Anyone who has diffed a specification against a live schema the night before handover will agree it is not an experience worth repeating.
Generating it from SQL
The fix is to reverse the direction. The source of truth is the schema, not the document. DDL already carries column names, types, nullability, defaults and keys — most of the required fields. What it lacks is the logical name and description, and COMMENT fills exactly that gap.
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Member ID',
email VARCHAR(255) NOT NULL UNIQUE COMMENT 'Email — login account',
nickname VARCHAR(50) NOT NULL COMMENT 'Display nickname',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Signup time'
) COMMENT 'Member';Paste commented DDL like this into WorksCove ERD and you get the diagram plus a table specification in the format above, exportable to Excel for review. For a database already in production you can skip the DDL export and pull the schema in by reverse engineering — over an SSH tunnel if the server isn't directly reachable.
Notice how COMMENT 'Member' became the table's logical name and each column comment landed in the description column. Relations and indexes were read from the schema, so nobody had to type them at all.
Three comment rules worth agreeing on as a team
The quality of the generated document is the quality of your comments. Three small conventions go a long way:
- One format. "Logical name — clarification" as a fixed shape keeps the generated columns consistent and the raw DDL readable.
- Coded values must be spelled out.
status TINYINT COMMENT 'Status: 1=active, 2=dormant, 3=closed'— without that line, the meaning of the codes exists only in someone's memory. - Numbers get units.
amount BIGINT COMMENT 'Payment amount (KRW)'. Whether it's dollars or won, megabytes or gigabytes, is unknowable without the comment.
If you're sitting on an uncommented production database, don't try to comment everything at once. Apply the rules to new and modified tables first, then backfill the core domain — the tables everyone reads, like members and orders.
Updates mean regenerating, not editing
The real payoff isn't the first generation — it's everything after. When the schema changes you regenerate rather than edit. There is no window in which the document can drift, and the question "which file is current" no longer comes up. The review process that wants a spreadsheet stays exactly as it is; only the work of producing that spreadsheet goes away.
Look back at the five reviewer flags: type mismatches (2), missing FK targets (3) and disagreement with the live database (5) all vanish at this point. What remains — matching logical names to screen vocabulary (1) and putting an as-of date on the cover (4) — is habit, not tooling.
FAQ
Is a table specification the same thing as a data dictionary?
They overlap heavily. A table specification documents the columns and constraints of each table, while a data dictionary is often broader and covers business terms across the whole system. In most projects the two are used interchangeably — follow whatever your team already calls it.
Does the specification have to be a spreadsheet?
Spreadsheets are convention, not standard. Reviewers and clients often ask for one, so the final deliverable is frequently Excel — but there is no reason to keep the source of truth there. Generate the document from the schema and export to Excel only when you hand it over.
How detailed should column comments be?
One logical name, plus one short clarifying phrase if needed. Two things are non-negotiable though: columns that hold coded values must list what the codes mean (1=active, 2=dormant), and numeric columns must state their unit. Those two habits do more for the document than any amount of long prose.
I need a specification for a database that is already running. Do I write it from scratch?
No. Export the DDL and paste it into an ERD tool, or connect directly if the tool supports reverse engineering. Columns, types, constraints and comments come across as the document. Typing it by hand only adds typos.
So: start from a template that covers the required fields, then get out of the business of filling that template in by hand as quickly as you can. Keep the source in the schema and generate the document, and most of the recurring work around specifications disappears.
Normalization — deciding how far to split your tables — is the natural next topic, and we'll work through it with the same forum schema.