DEV Community: Donghyun Park

Database Naming Conventions: A Practical Guide for Tables and Columns

Donghyun Park — Wed, 16 Sep 2026 04:54:21 +0000

Every schema ends up with a naming convention, whether anyone chose one or not. The question isn't whether you'll have a convention — it's whether it's the same convention everywhere, or three different ones fighting each other across users, tbl_orders, and OrderItem.

Why naming conventions matter more than the convention

The specific rules you pick matter less than you'd think. What matters is that everyone follows the same ones. A schema where every table uses snake_case and every table uses camelCase are both fine in isolation. A schema where half the tables use one and half use the other is a liability.

Here's a concrete version of the cost. Say your users table has a primary key column named id, but a migration two years ago added a denormalized copy of it to an orders table as userId, and a third table — added by a contractor who was used to a different codebase — calls it usr_id. Now:

A new engineer writing a join has to grep the schema (or worse, guess) to find out which spelling applies to which table.
Search tooling breaks. Searching for user_id across the codebase silently misses every reference to userId and usr_id.
Code generation and ORMs that infer foreign keys from naming patterns (user_id → users.id) fail silently on the columns that don't match, and someone has to hand-write the mapping.
Onboarding takes longer, because "how do we name things" isn't answerable by looking at one example — it requires reading enough of the schema to notice the exceptions.

None of this is expensive to prevent. It's expensive to unwind once forty tables depend on the inconsistency. That asymmetry is the entire argument for writing a convention down early, even a short one, and enforcing it before the schema grows.

Case style: pick snake_case and move on

The pragmatic reason to prefer snake_case for tables and columns isn't aesthetics — it's that SQL databases don't agree on how they treat identifier case, and snake_case is the one style that survives all of them unscathed.

PostgreSQL folds unquoted identifiers to lowercase. Write CREATE TABLE UserAccount, and Postgres stores it as useraccount. Query it as UserAccount without quotes and it still resolves to useraccount — but the moment anyone quotes it ("UserAccount"), Postgres treats that as a different, case-sensitive identifier. Mixed-case naming plus inconsistent quoting is how you end up with two tables that look like one.
MySQL ties table-name case sensitivity to the underlying filesystem and the lower_case_table_names server setting. A schema that works fine in development on Windows or macOS can break in production on Linux because table names became case-sensitive.
Oracle folds unquoted identifiers to uppercase, the mirror image of Postgres, with the same quoting trap.

snake_case sidesteps all of it because it never depends on case folding being consistent — there's no mixed case to fold incorrectly in the first place.

-- Bad: relies on case being preserved and matched consistently
CREATE TABLE UserAccount (
  Id INT PRIMARY KEY,
  FirstName VARCHAR(100),
  CreatedAt TIMESTAMP
);

-- Good: unambiguous under every dialect's folding rules
CREATE TABLE user_account (
  id INT PRIMARY KEY,
  first_name VARCHAR(100),
  created_at TIMESTAMP
);

Pick snake_case, write it in the team's style guide, and stop relitigating it per pull request.

Singular or plural table names?

Both sides have a real argument. The singular camp says a table row represents one instance of an entity, so user reads correctly as "the user table" and composes cleanly with singular model class names. The plural camp says a table is a collection of rows, so users reads correctly as "the set of all users," which is how you'd describe it in English and how most ORMs (Rails, Django) default their table-name inference.

Neither argument wins outright — frameworks and prominent open-source schemas exist on both sides. The only real mistake is not deciding.

Style	Example	Reads as
Singular	`user`, `order_item`	"the user table" — one row, one instance
Plural	`users`, `order_items`	"the users table" — a collection of rows

Pick one, write it down, and apply it to every table without exception — including join tables and lookup tables, which are the ones people forget to check against the rule.

Abbreviations: the silent killer

Case style and pluralization are visible, one-time decisions. Abbreviations are worse, because they accumulate silently, one column at a time, usually introduced by whoever is typing fastest that day. cust shows up next to cstmr, amt sits beside amount, and six months later nobody can tell you which one is correct because both are "correct" — they're both in production.

The fix isn't "abbreviate less" or "abbreviate more." It's a single approved abbreviation list: a short, shared table that maps full terms to their one sanctioned short form, checked in reviews the same way you'd check a variable name against a linter.

Term	Approved abbreviation
identifier	`id`
quantity	`qty`
amount	`amt`
description	`desc`
customer	`cust`

The list doesn't need to be long, and it doesn't need every abbreviation you'll ever use — it needs to exist, be visible to everyone writing DDL, and win every time someone is tempted to invent a new shorthand on the spot. The deeper version of this mechanism — deriving every physical name from business terms through the list, instead of checking names against it after the fact — is the subject of logical vs. physical data models.

It's also the step the international standard leaves to you. ISO/IEC 11179 specifies how to compose a data element name from controlled vocabularies, but stops short of turning that name into a column — which is exactly why an abbreviation list, not the standard, is what decides whether two teams produce the same schema.

Keys, foreign keys, and constraint names

Primary keys have a real trade-off between id and <table>_id. A bare id is short and consistent — every table's primary key is spelled the same way, and SELECT id FROM orders reads cleanly. <table>_id (e.g., order_id on the orders table) is more verbose but disambiguates automatically in joins and in any context where the column travels without its table name attached — log lines, API payloads, CSV exports. Either is defensible; what breaks things is doing id on some tables and <table>_id on others.

Foreign keys should name themselves after what they reference, not after the local table: a orders row referencing customers gets customer_id, not order_customer or bare customer. That naming makes the join implicit — orders.customer_id = customers.id reads correctly even to someone who's never seen the schema.

Constraint names matter more than people give them credit for, because they're the string that shows up in the error message when the constraint fires in production. A predictable pattern makes those errors greppable instead of cryptic.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_number VARCHAR(20) NOT NULL,
  CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT uq_orders_order_number
    UNIQUE (order_number)
);

fk_<table>_<referenced_table> and uq_<table>_<columns> are enough structure to make any constraint violation self-explanatory from the name alone — no need to go look up what orders_customer_id_fkey (Postgres's auto-generated default) actually constrains.

Reserved words and portability

Avoid naming tables or columns after SQL reserved words — order, user, group, select, table — even when the database in front of you happens to tolerate it unquoted or with minimal fuss. The problem isn't today's dialect; it's every future context the name travels through: a different database engine after a migration, a query builder that doesn't quote identifiers by default, a raw SQL string pasted into a script without the quoting the ORM would have added automatically. order is a particularly common trap because it's also an extremely natural table name for e-commerce schemas — customer_order or purchase_order costs you nothing and removes the trap entirely. The general rule of portability: assume your schema will eventually be queried by a tool, dialect, or person that doesn't share your current database's tolerances, and name defensively for that case rather than the one in front of you.

Making it stick: from wiki page to enforcement

A naming convention that lives only on a wiki page gets followed for about two weeks. After that, deadlines happen, someone new joins without reading the wiki, and the first exception becomes precedent for the next five. Documentation alone doesn't hold a line — it just tells you what the line was supposed to be.

The realistic path is to escalate enforcement in stages, matching effort to how much the convention has already drifted:

Write it down — a short style guide (case, pluralization, the abbreviation list, key/constraint patterns) that's easy to skim, not a spec nobody reads.
Put it in code review — a checklist item reviewers actually check against new migrations, not an assumption that people remember the wiki page.
Automate it — lint DDL in CI against the documented rules, or generate physical names from a shared word list so the convention is applied mechanically instead of remembered by each contributor. A tool-neutral version of this: keep one word list of approved terms and abbreviations, and derive every physical column name from it programmatically rather than typing each one by hand — that's the only stage where "follow the convention" stops being a matter of individual discipline.

Most teams stop at stage one and wonder why the schema still drifts. Stage two catches most of what's left. Stage three is the only stage that survives a team turning over.

That shared word list has a companion beyond naming: a term's definition belongs in the same model as the words that name it, so the convention and the meaning don't end up in separate documents that drift apart — see data dictionary vs business glossary for why keeping them together matters.

Originally published at sqemo.com. Sqemo is an ERD tool that enforces database naming conventions — free in the browser, no signup.

Building a Schema With an AI Agent Without Naming a Single Column

Donghyun Park — Wed, 09 Sep 2026 11:45:27 +0000

Ask an AI agent for a discussion-board schema and you'll get one in about four seconds. Here is the part nobody mentions: you'll also get is_deleted in this table and del_yn in the next one, because the model is pattern-matching against every schema on GitHub, and those schemas disagree with each other and with you.

The fix is not a better prompt. It is giving the agent the same thing you'd give a new hire — the list of words your team actually uses — and making it impossible to ignore.

I did that with a threaded discussion board. I never typed a column name. The whole run fits in three and a half minutes:

The setup, in one call

The agent starts by binding a new project to our team standard:

create_erd {
  name: "Threaded Discussion Board",
  workspaceId: "…",
  target: { server: true }
}

get_erd_overview
// → { glossaryLinked: true, dictionaryEntryCount: 28, domainCount: 15 }

Twenty-eight words and fifteen domains arrive with the project. That is the whole trick. From here the agent isn't choosing names — it's resolving them.

Describing the work

Then I talked to it the way I'd talk to a colleague: members post articles, a post can reply to another post, members comment on posts.

The agent called the tools with logical names — business terms, not column names:

upsert_entity    { logicalName: "Post" }              // → POST
upsert_attribute { logicalName: "Post Content",
                   domain: "Content" }                // → POST_CNTS
upsert_attribute { logicalName: "Delete Flag",
                   domain: "Flag" }                   // → DELETE_YN

Content → CNTS and Flag → YN aren't the model's taste. They're our word list's abbreviations, applied exactly as they were applied in every table we've built before. The domain carries the type, so Content is varchar(1000) everywhere — nobody decides column widths per table anymore.

Notice what didn't happen: I didn't review a name and correct it. There was nothing to correct.

The part most tools get wrong

Then the board needed threading — a post that replies to another post. A self-referencing foreign key.

This is where diagram tools quietly fall over. The child column can't reuse the parent's name (POST_NO referencing POST_NO in the same table), so something has to give it a role. Most tools either refuse, or let you type whatever you want, which is how you end up with parent_id in one table and prnt_post_no in another.

Here the role prefix is itself a naming rule, resolved through the same word list as everything else:

`PARENT_POST_NO` varchar(20) COMMENT 'Parent Post Number'

Register Parent → PRNT in the word list and it becomes PRNT_POST_NO instead — team-wide, in every self-referencing relationship anyone ever draws. The prefix is a decision made once, by the standard's owner, not per-table by whoever is typing.

What happens when a word is missing

Real modelling hits words nobody registered. Ours didn't have "Delete", which is why DELETE_YN came out unabbreviated. The lint said so:

lint_erd
// → { code: "unknown-word", severity: "warning", objectName: "Delete Flag" }

The agent's move here is the interesting one. It does not invent DEL. It files a proposal:

propose_dictionary_word {
  logicalWord: "Delete", physicalWord: "DELETE", abbreviation: "DEL",
  note: "Found while modelling the discussion board"
}
// → { status: "pending" }

A human approves it, and the abbreviation propagates to every connected project. The agent is a proposer, not an authority. That distinction is the entire difference between an assistant and a liability — and it's the thing a chatbot with no shared state cannot do, no matter how good the model gets.

Checking work that already exists

The same computation runs backwards. Give it a name someone already wrote and it tells you what the standard would have produced:

check_naming { logicalName: "Customer Phone Number",
               physicalName: "CUSTOMER_PHONE_NUMBER" }
// → { generatedPhysicalName: "CUST_TEL_NO", compliant: false }

That runs in CI with no agent involved — npx sqemo-mcp lint schema.erd.json exits 1 on violations. Humans, agents, and pull requests all get held to the same computation.

The output

CREATE TABLE `POST` (
  `POST_NO`        varchar(20)   NOT NULL COMMENT 'Post Number',
  `POST_CNTS`      varchar(1000) NOT NULL COMMENT 'Post Content',
  `DELETE_YN`      char(1)       NOT NULL DEFAULT 'N' COMMENT 'Delete Flag',
  `PARENT_POST_NO` varchar(20)            COMMENT 'Parent Post Number',
  PRIMARY KEY (`POST_NO`)
);

The business terms survive as column comments, so the meaning reaches the database instead of dying in a diagram nobody opens.

What it doesn't do

Being straight about the edges:

The self-reference prefix is one global rule, not a per-relationship choice. It defaults to Parent, and abbreviating it means registering Parent in the word list like any other word. A project that already has a self-referencing relationship keeps the prefix those columns were built with, so their names don't shift underneath it.
Naming rules aren't retroactive. Change a rule and existing physical names keep what they have. Self-referencing FKs are the exception: their names are derived, so they get rewritten on their next edit.
The agent still models badly if you describe the work badly. Nothing here checks whether your entities make sense — only that they're named the way your team agreed.

The point

Look at who decided what:

Decision	Made by
Which tables and relationships exist	Me, in business terms
Abbreviations, separators, case	The team standard
Data types	Domains
New words entering the standard	The standard's owner, by approval

The agent did the typing and none of the deciding. A naming convention in a wiki asks every person and every tool to remember it. This one is computable, which means it can be applied, checked, and enforced without anyone remembering anything.

The step-by-step version, with every call and its output, is in the walkthrough. If you want the argument before the tutorial, an ERD MCP server covers what these tools are and where they stop; database naming conventions covers what to put in the word list in the first place.

Try it in the app — no signup — or point your agent at npx sqemo-mcp.

Originally published at sqemo.com. Sqemo is an ERD tool that enforces database naming conventions — free in the browser, no signup.

An ERD MCP Server: AI Agents That Follow Your Naming Standard

Donghyun Park — Fri, 28 Aug 2026 01:04:15 +0000

AI agents already write database schemas. Ask Claude or Cursor for a feature and the migration file comes back with tables, columns, and foreign keys — named however the model's training data leans that day. user_id here, userId there, usr_no when it read one too many legacy dumps. The agent isn't wrong; it just has no idea your team writes cust_no, because your naming convention lives in a wiki the model has never seen.

That's the actual problem an ERD MCP server solves. Not "AI can draw diagrams now" — but that schema work done by agents can follow the same standard as schema work done by people. This post explains what sqemo-mcp does, how the naming part works, and — in the same honest-comparison spirit as our dbdiagram comparison — what it doesn't do yet.

What an MCP server is, in one paragraph

MCP (Model Context Protocol) is the open standard for giving AI agents tools. A server exposes typed operations — "list entities," "add an attribute," "export SQL" — and any MCP-capable client (Claude Code, Claude Desktop, Cursor, Codex, and a growing list) can call them. Instead of the agent hallucinating your schema from half-remembered context, it reads the real one and edits it through operations that enforce your rules.

What an agent can do with your ERD

sqemo-mcp exposes 36 tools over your ERD files. Grouped by what they're for:

Read and edit the model. get_erd_overview, get_entity, upsert_entity, upsert_attribute, upsert_relationship, and their delete counterparts. The agent works on the same .erd.json file you edit in the app — one source of truth, not a parallel copy.
Import and export. import_sql and export_sql in seven dialects (MySQL, PostgreSQL, Oracle, SQL Server, SQLite, H2, CUBRID), plus DBML both ways. Paste a raw pg_dump -s or mysqldump output and it parses. "Import this legacy dump and normalize the names against our word list" is a one-prompt task.
Generate and check names. generate_physical_name, check_naming, search_dictionary — more on these below, because they're the point.
Quality and hygiene. lint_erd, validate_erd, diff_erds, auto_layout.
Propose, don't decree. When the agent needs a word that isn't in the team word list, propose_dictionary_word files a proposal into the team's approval queue instead of silently inventing an abbreviation. A human approves it; then it's standard.

Why the naming tools are the interesting part

Most schema tools treat names as free text, so an agent's naming is only as good as its prompt. Sqemo treats the physical name as computed: you register a word list (customer → cust, number → no) and naming rules once, and the physical column name is generated from the logical one — same input, same output, for humans and agents alike.

That changes what the agent is asked to do. It doesn't guess that your team abbreviates "customer" as cust — it says "Customer Number" and generate_physical_name returns cust_no, per your rules, deterministically. And because the correct name is computable, drift is detectable: check_naming and lint_erd flag any column that deviates from what the word list would generate. The agent's output is held to the same standard as a human's — mechanically, not by code-review vigilance. If you want the fuller argument for generating names from a word list, that's its own post.

Setup

Node.js 22+, no install — it runs via npx. For Claude Code, add to .mcp.json at your project root:

{
  "mcpServers": {
    "sqemo": { "command": "npx", "args": ["-y", "sqemo-mcp"] }
  }
}

Claude Desktop and most other clients take the same mcpServers JSON; the docs page has per-client paths. Local .erd.json files work fully offline with no account. To let an agent touch ERDs saved on the Sqemo server, sign in once with npx sqemo-mcp login — credentials are stored locally, and your password itself never is.

The same enforcement, without an agent

The package doubles as a plain CLI, which is how the standard reaches CI:

npx sqemo-mcp lint schema.erd.json   # exit code 1 on naming violations

Put that in a GitHub Action and a pull request that drifts from the word list fails the build — the same check the agent runs interactively, enforced mechanically on every merge. There's also export for generating SQL or DBML in a pipeline. If you're deciding what artifact should be the source of truth in the first place, we wrote about DBML vs SQL DDL — the .erd.json model sits a layer above both, and the CLI derives either from it.

What it doesn't do (yet)

Honest limits, so you can decide with eyes open:

It's a local stdio server, not a hosted one. It runs on your machine via npx. There's no remote HTTPS endpoint yet, so clients that only support hosted MCP servers can't connect — remote MCP is on our roadmap, not shipped.
The server code is open, the app isn't. The MCP server is on npm and the official MCP Registry (io.github.sqemo/sqemo); the web app itself is not open source.
Three tools are paid. export_alter_sql (migration ALTER scripts from a baseline diff) and the two live-database tools, introspect_db and check_db_drift (checking the ERD against a real database), are Pro features ($9/mo). The other 33 tools, including everything above, work on the free plan.

Where this fits

If your schema work is entirely human and entirely solo, an MCP server is a nice-to-have. The moment either stops being true — an agent writes your migrations, or more than one person names columns — the question becomes how the standard gets enforced. Our answer: make the correct name computable, then let humans, CI, and agents all be checked against the same computation. Try it in the app (no signup), or point your agent at npx sqemo-mcp and ask it to import your schema. If you'd rather see the whole thing end to end first, the walkthrough goes from an empty project to standards-compliant DDL — including the case most tools get wrong, a post that replies to another post. It's also a three-and-a-half-minute video, if reading is not your thing today.

FAQ

What is an ERD MCP server?

An MCP (Model Context Protocol) server that exposes your entity-relationship diagram to AI agents as typed tools — list entities, add an attribute, import SQL, export DBML, check a name against the naming standard — so the agent reads and edits the real model instead of guessing the schema from context.

Which AI clients work with sqemo-mcp?

Any MCP-capable client that supports local stdio servers: Claude Code, Claude Desktop, Codex CLI, Cursor, and others. It runs via npx (Node.js 22 or later) with a one-line mcpServers entry; no separate install.

Is the Sqemo MCP server free?

Yes. 33 of its 36 tools, including import, export, naming generation, and lint, work on the free plan with no account for local .erd.json files. Three tools are Pro ($9/month): export_alter_sql, introspect_db, and check_db_drift.

Is it a hosted (remote) MCP server?

Not yet. sqemo-mcp is a local stdio server that runs on your machine via npx. Clients that only support hosted HTTPS MCP endpoints can't connect today; remote MCP is on the roadmap.

Originally published at sqemo.com. Sqemo is an ERD tool that enforces database naming conventions — free in the browser, no signup.