Database Naming Conventions: A Practical Guide

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.

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.

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.

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:

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 against the documented rules, or generate physical names from a shared glossary so the convention is applied mechanically instead of remembered by each contributor. A tool-neutral version of this: keep one glossary 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.