# Database Naming Conventions: A Practical Guide for Tables and Columns

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.

```sql
-- 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](https://sqemo.com/blog/logical-vs-physical-data-models/).

It's also the step the international standard leaves to you. [ISO/IEC 11179](https://sqemo.com/blog/iso-11179-naming-convention/) 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.

```sql
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:

1.  **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.
    
2.  **Put it in code review** — a checklist item reviewers actually check against new migrations, not an assumption that people remember the wiki page.
    
3.  **Automate it** — [lint DDL in CI](https://sqemo.com/docs/mcp/) against the documented rules, or [generate physical names from a shared word list](https://sqemo.com/docs/naming-standards/) 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](https://sqemo.com/blog/data-dictionary-vs-business-glossary/) for why keeping them together matters.

* * *

*Originally published at* [*sqemo.com*](https://sqemo.com/blog/database-naming-conventions/)*. Sqemo is an ERD tool that enforces database naming conventions — free in the browser, no signup.*