Naming Things in SQL: Conventions That Survive Team Growth

The Cost Is Hesitation, Repeated

Naming debates are easy to dismiss as bikeshedding, and individual instances genuinely are. The cost does not come from any single bad name. It comes from the repeated moment of doubt.

Is the table user or users? Is the timestamp created_at or create_date or date_created? Is the foreign key customer_id or fk_customer or just customer? Each question costs a few seconds and a lookup. Multiply by every query, every developer, every day.

Worse, inconsistency compounds. Once a schema contains both conventions, the next person has to choose, and whichever they pick makes the schema slightly more inconsistent. Nobody is wrong at any step, and the result deteriorates anyway.

The value of a convention is almost entirely in its consistency, not its content. Which one you pick matters far less than picking one and applying it everywhere.

The Decisions Worth Making Once

Singular or Plural Table Names

Both have coherent arguments. Plural reads well in queries because a table holds many rows. Singular reads well in joins and maps directly to an entity name in code, and it sidesteps irregular plurals entirely — no arguing about person versus people.

Pick one. The genuinely bad outcome is a schema with both, where every query requires a lookup.

Case and Separators

Use snake_case in lowercase. This is not merely aesthetic: unquoted identifiers are folded to lowercase by PostgreSQL and to uppercase by Oracle, and MySQL's case sensitivity depends on the host filesystem. A camelCase identifier therefore requires quoting to survive, and quoted identifiers are easy to forget and produce confusing errors.

Lowercase snake_case avoids the entire class of problem and is portable everywhere.

Primary Keys

The choice is between a bare id on every table and a prefixed customer_id. The prefixed form has one concrete advantage: after a join, id is ambiguous and requires qualification, while customer_id is not. It also lets you use USING in joins.

The bare form is more common and integrates more smoothly with ORMs that assume it. Either works; the prefixed form causes marginally less friction in queries with many joins.

Foreign Keys

Name the foreign key after the column it references, so the relationship is visible without consulting the schema. A column named customer_id obviously points at customers. A column named ref_2 requires investigation.

When a table has two foreign keys to the same target, qualify by role rather than numbering: sender_customer_id and recipient_customer_id, not customer_id_1 and customer_id_2. The role is the information a reader needs.

Timestamps and Booleans

Standardise the timestamp suffix — _at for points in time, _on for dates if you want to distinguish them — and use it without exception. Store timestamps with timezone awareness and record what the value means: shipped_at is unambiguous, ship_date could be planned or actual.

For booleans, prefix with is_ or has_ so the column reads as a predicate, and always state the positive. is_active is clear; is_not_disabled requires a double negative every time it appears in a condition.

Conventions at a Glance

Two entries deserve emphasis. Units in the name — _cents, _seconds, _bytes — prevent an entire category of bug that no type system catches. And naming indexes explicitly matters because auto-generated names differ between environments, which makes migration scripts non-portable.

What Genuinely Does Not Matter

Being clear about this is part of making the convention adoptable. Arguing about everything makes people ignore the whole document.

• Singular versus plural, as a choice. Consistency matters; which one you chose does not.
• Keyword case in queries. Uppercase keywords are conventional, but a formatter normalises this automatically, so it needs no human enforcement or review comment.
• Indentation and line breaks. Same reasoning. Run everything through a formatter — the SQL Utility Tools formatter does this locally in the browser — and stop discussing it in review. Mechanical concerns should be handled mechanically.
• Alias length. Short aliases are fine in a small query. What matters is that they are meaningful in a large one, and that is a judgement rather than a rule.

Fixing an Inconsistent Schema

Most teams adopting conventions already have a schema that violates them. A big-bang rename is tempting and usually a mistake, because renaming a table breaks every query, view, function, and application reference at once.

A staged approach:

1. Write the convention down and apply it to everything new. This stops the problem growing while you decide what to do about the past. It is the highest-value step and the cheapest.
2. Record the exceptions. A short list of legacy names that violate the convention, with a note that they are known. This prevents the same discussion recurring and prevents someone "fixing" one in isolation.
3. Rename opportunistically, behind a view. When a table is already being changed, create the new name and leave a view under the old one. Readers keep working while you migrate them. This is the expand-migrate-contract pattern applied to naming.
4. Never rename a column and change its meaning in one migration. If amount becomes amount_cents and the stored values change scale, do those as two separate deployments. Combining them makes it impossible to tell whether a wrong figure came from the rename or the rescale.
5. Accept that some names will never be fixed. A well-documented inconsistency costs far less than a risky migration of a heavily-referenced table.

Frequently Asked Questions

Singular or plural table names, definitively?

There is no defensible universal answer, which is why the debate never ends. Singular avoids irregular plurals and maps cleanly to entity names in code; plural reads more naturally in a FROM clause. Choose based on what your ORM and existing schema already lean toward, write it down, and move on. The consistency is the whole benefit.

Should I prefix tables to group them by module?

Use schemas or namespaces instead where your engine supports them, since that is the purpose-built mechanism and it also gives you permission boundaries. Prefixes are a reasonable fallback on engines without real schema support, but they make names longer and the grouping is not enforced by anything.

Is putting the unit in a column name really necessary?

Yes, and it is one of the highest-value conventions on the list. A column called duration holding milliseconds while a nearby one holds seconds is a bug waiting to happen, and no type system will catch it because both are integers. Naming the unit makes the mismatch visible at the point of use.

Conclusion: Decide Once, Enforce Mechanically

Naming conventions save time not because good names are faster to read, but because settled questions cost nothing to answer. A schema where every element follows a predictable pattern lets people write queries without lookups.

Write the convention down, keep it short, apply it to everything new, and be explicit about which legacy names are permanent exceptions. Delegate formatting entirely to a tool so it never appears in review. The goal is not elegance; it is removing thousands of small hesitations.