Database Naming Conventions Guide

Database naming conventions prevent confusion when a team picks one pattern per decision — singular or plural tables, is_active or active, user_id or userId — and applies it everywhere without exception. Ambiguity here isn't cosmetic: it produces wrong joins, silent type bugs, and slower onboarding for every engineer who inherits the schema.

Quick answer: Use singular table names, prefix every boolean column with is_, has_, or can_, name foreign keys after the singular referenced entity plus _id (user_id, not userId or fk_user), and never use a bare SQL reserved word as a column name. Write the rule down once, in a one-page document, and enforce it in code review.

Why Naming Is a Communication Decision, Not Bikeshedding

Naming feels like a style preference because it costs nothing to type either way, but every table and column name is permanent documentation that every future query, migration, and new hire depends on. Get it wrong once and you haven't been sloppy — you've shipped a bug that compiles.

Naming conventions are one small piece of the larger discipline of building a schema that holds up under real use — for the full picture of entities, relationships, normalization, and the decisions that come before naming, see the complete guide to data modeling.

Consider what a name actually does. A column called status with no table prefix, no enum reference, and no comment forces every engineer who touches it to go read the application code to learn its allowed values. Multiply that lookup cost by every table in a schema with 40, 100, or 400 tables, and "just look at the code" stops being a real answer.

This is the same discipline good discovery work already demands. A team writing a JTBD statement has to agree on precise language for the customer's underlying need before the debate about solutions can even start — see the complete guide to jobs-to-be-done for how that forcing function works. Mapping a customer journey forces the same kind of agreement on stage names before anyone argues about the details inside a stage. Schema naming is the same forcing function applied to data.

Naming drift compounds because renaming a live column is one of the most expensive refactors a team can undertake. Martin Fowler and Pramod Sadalage, in Refactoring Databases: Evolutionary Database Design, document why: a rename touches every reader and writer of a table at once, so it needs an expand-contract migration — add the new name, dual-write, backfill, cut over readers, then drop the old name — not a single atomic change. A mistake caught in week one costs a pull-request comment. Caught in year two, it costs a migration project.

The cost shows up in aggregate, too. Stripe's 2018 Developer Coefficient report, based on a commissioned developer survey, estimated that engineers lose roughly a third of their working time to technical debt and bad code — and inconsistent schema naming is a direct, everyday contributor to that tax, since every ambiguous name adds a small verification step to every query someone writes against it.

Three concrete failure modes make this tangible:

Wrong joins. A junior engineer joins orders.user_id to customers.id instead of users.id because two tables both plausibly own "the customer," and nothing in the naming disambiguates which one is authoritative.
Silent type bugs. A column named active gets treated as a string "true"/"false" in one service and a real boolean in another, because the name alone gave no signal about the intended type.
Slow onboarding. A new hire spends their first two weeks reverse-engineering table relationships from inconsistent names instead of reading a five-minute convention document.

None of this is about aesthetics. It's about whether your schema can be read correctly by someone who wasn't in the room when it was designed — which, on any team past a handful of people, is most of the people who will ever query it. If you haven't nailed down what an entity even is in your domain first, naming decisions will keep sliding around underneath you — see what actually counts as an entity before you standardize names for it.

Singular vs. Plural Table Names: Pick One and Enforce It

Neither singular (user) nor plural (users) table naming is objectively correct, but the decision matters because it has to be applied consistently across every table, every join, and every ORM convention your team relies on. The strongest argument for either side is consistency with your ORM's default, not some inherent grammatical correctness.

The case for plural tables (users, orders, line_items) is that a table represents a collection of rows, and English pluralizes collections. This is also the default in two of the most widely used web frameworks: Ruby on Rails' Active Record and Laravel's Eloquent both auto-pluralize model names into table names, so fighting the convention means overriding a default in every model file.

The case for singular tables (user, order, line_item) is that a table represents an entity type, and a row is one instance of it — so the name should read the same whether you're describing one row or the whole table. Joe Celko, a longtime voice on SQL style, has argued for singular, entity-style naming precisely because it keeps FROM clauses and column-to-table cross-references easier to reason about (order.order_id reads more cleanly than the plural form once you're deep in a multi-join query).

Convention	Example	Framework default	Strongest argument for it
Plural	`users`, `orders`	Rails Active Record, Laravel Eloquent	A table is a collection; collections pluralize
Singular	`user`, `order`	Django-style model naming, many enterprise data warehouses	A table is an entity type; naming matches the entity, not the count

The table above isn't an argument for one convention winning outright — it's evidence that your ORM's default should usually decide the fight for you. If you're on Rails, fighting pluralization costs more than it's worth. If you're hand-writing DDL or working in a warehouse with no ORM opinion, singular tends to read more consistently once you're several joins deep.

Junction Tables Break the Rule on Purpose

Many-to-many join tables are the one place singular-vs-plural conventions should bend. A join table between users and projects reads clearest as user_projects or project_users — singular entity names joined together, not pluralized as a unit. Pick one ordering rule (alphabetical, or "owning side first") and apply it everywhere, so nobody has to guess whether it's user_projects or projects_users on any given table.

Whatever you decide, put it in the same place your team already documents entities and relationships. If you haven't yet drawn the relationships out visually, do that before you finalize names — see why the data model is the product and should be drawn as an ERD first for why visualizing the shape prevents naming decisions that don't survive contact with a real join.

Boolean Columns: `is_active` or `active`, and the Verb-Prefix Rule

Boolean columns should carry a verb prefix — is_active, has_verified_email, can_edit — because the prefix is what tells a reader, at a glance and without opening documentation, that the column is a true/false flag rather than a status string or a foreign key. A bare adjective like active is genuinely ambiguous in a way is_active is not.

This is the same instinct behind Joel Spolsky's well-known argument for "Apps Hungarian" notation: a naming prefix that encodes intent, not implementation, helps a reader catch a class of bug — using a value in a way its name warns against — before it ships. A boolean prefix is exactly that: cheap to type, and it removes an entire category of "wait, is this a flag or a label?" confusion.

Style	Example	Reads unambiguously as boolean?	Common failure mode
Verb-prefixed	`is_active`, `has_paid`, `can_publish`	Yes	Rare — prefix vocabulary needs light governance (`is_` vs `has_` vs `can_`)
Bare adjective	`active`, `verified`, `deleted`	No	Gets confused with a status enum or nullable tri-state column
Past-tense verb	`activated`, `verified_at`	Ambiguous — could be boolean or timestamp	Column named like a boolean actually stores a datetime, or vice versa

Three rules make the prefix convention hold up in practice:

Reserve is_ for state, has_ for possession, and can_ for permission. is_active describes a state the row is in; has_subscription describes something the row owns; can_export describes a permission the row grants. Mixing these up (is_export for a permission) reads wrong even before anyone checks the schema.
Never let a boolean column go nullable without a documented reason. A NULL in a boolean column means "unknown," which is a legitimate tri-state, but only if the team agreed that's what it means — otherwise NULL silently becomes a fourth undocumented value alongside true and false.
Don't reuse a boolean where a timestamp says more. is_deleted throws away when something was deleted; deleted_at (nullable) gives you the boolean for free (deleted_at IS NOT NULL) plus an audit trail. This is exactly the trade-off covered in the comparison of soft-delete versus hard-delete modeling — worth reading before you commit to a flag over a timestamp.

Foreign Keys: `user_id`, `userId`, or `fk_user` — Pick One Pattern

Foreign keys should be named after the singular referenced entity plus _id — user_id referencing users.id, project_id referencing projects.id — in snake_case, because that pattern is self-documenting about both the relationship and the target column, and it survives translation across your database, your ORM, and your API layer without anyone needing a lookup table to remember the mapping.

The fk_ prefix style (fk_user, fk_project) is common in some enterprise SQL Server shops, but it duplicates information the database already tracks in its constraint metadata, and it reads worse in a WHERE clause or a join condition than the plain user_id form. Save fk_ for the name of the constraint itself (fk_orders_user_id), not the column.

camelCase foreign keys (userId) cause a specific, avoidable problem in Postgres and most SQL dialects: unquoted identifiers fold to lowercase, so an unquoted userId silently becomes userid the moment it's created. From then on, every reference must either match that folded form or be wrapped in double quotes forever to preserve the case.

PostgreSQL's own documentation describes this identifier-folding behavior explicitly, and it's the single most common source of "why can't Postgres find my column" questions from teams migrating from a camelCase-native language. Pick snake_case for the database layer and let your ORM handle camelCase translation at the application boundary — most ORMs (Prisma, Sequelize, ActiveRecord) already do this for you.

Self-Referencing Foreign Keys

A table that references itself — an employees table with a manager_id pointing back at employees.id, or a comments table with a parent_comment_id — needs a name that describes the relationship, not a repeat of the table name. manager_id is clear; employee_id_2 is not. When a table has more than one foreign key into the same target table (an orders table with both billing_address_id and shipping_address_id pointing at addresses), the relationship name is doing all the disambiguation work, so don't skip it.

Reserved Words and Other Naming Landmines

Avoid naming any table or column after a word reserved by your SQL dialect — order, user, group, table, index, level — because it forces every query against that column into quoting or escaping. One dialect's reserved list rarely matches another's, so a name safe in MySQL might break the day you migrate to Postgres or add a BigQuery export.

user is the classic trap: it's reserved in PostgreSQL and MySQL (as a permissions-related keyword), which is why so many production schemas end up with users as the table name even on teams that otherwise prefer singular tables — the reserved-word collision, not a grammar preference, made the decision for them.

Word	Reserved in	Why it breaks	Safer alternative
`order`	ANSI SQL, MySQL, Postgres, SQL Server	Collides with `ORDER BY`	`orders`, `customer_order`
`user`	PostgreSQL, MySQL	Collides with permissions/auth keyword	`users`, `app_user`
`group`	ANSI SQL (`GROUP BY`)	Collides with grouping clause	`groups`, `user_group`
`table`	ANSI SQL	Collides with `CREATE TABLE` / DDL syntax	Rarely needed as a name at all
`key`	Many dialects	Collides with key/constraint syntax	`api_key`, `sort_key`
`date`	Not universally reserved, but heavily overloaded	Shadows the built-in type name, hurts readability	`created_date`, `due_date`

Beyond the classic reserved-word list, three related landmines deserve the same discipline:

Case sensitivity across environments. A name that works unquoted on your local Postgres instance may behave differently in a data warehouse or a case-sensitive Snowflake schema. Stick to lowercase snake_case everywhere and this stops being a variable.
Abbreviations without a shared glossary. qty, amt, and desc save four keystrokes and cost every new hire a Slack message asking what they mean. Spell it out (quantity, amount, description) unless the abbreviation is truly universal in your domain.
Generic, unqualified names on shared columns. A name column on five different tables (users.name, products.name, projects.name) is fine in isolation but painful the moment a query joins two of them and both columns collide in the result set, forcing an alias every single time. Consider full_name, product_name, or accept the alias cost consciously rather than by accident.

The metadata-registry standard ISO/IEC 11179, used by government and enterprise data-governance programs, formalizes this same idea at a larger scale: it defines naming and definition principles specifically so that a data element's name is unambiguous and consistently understood across systems that didn't originally share a data dictionary. You don't need the full standard to get the benefit — the principle (one name, one meaning, documented once) is the whole point.

A One-Page Naming Convention You Can Adopt Today

A naming convention only works if it fits on one page and gets enforced in code review, because a 40-page style guide nobody reads changes nothing, while a short, memorable rule set gets applied even under deadline pressure. Below is a convention you can copy, adapt, and put in your team's wiki this week.

The one-page convention:

Tables: singular, snake_case (user, project) — or plural if your ORM defaults to it (users, projects). Pick one, note the exception for junction tables (user_projects).
Columns: snake_case, always. No camelCase in the database layer, ever — translate at the ORM/API boundary instead.
Primary keys: id on every table, always the same type (bigint or uuid, chosen once for the whole schema).
Foreign keys: <singular_referenced_table>_id (user_id, project_id). For self-references or multiple FKs to the same table, name by relationship (manager_id, billing_address_id).
Booleans: is_, has_, or can_ prefix, never nullable without a documented reason.
Timestamps: created_at, updated_at, deleted_at (nullable, for soft delete) — consistent suffix, always UTC.
Reserved words: never used bare as a column or table name; check your dialect's reserved list before naming anything.
Abbreviations: banned unless the abbreviation is in a shared, written glossary the whole team has seen.

This is intentionally short enough to paste into a pull-request template as a checklist. The goal isn't exhaustive coverage of every edge case — it's a default strong enough that most schema decisions never need a discussion at all.

Where This Convention Meets Your Modeling Tool

A convention that only lives in a wiki page drifts the moment someone's in a hurry. This is where Prodinja's Data Modelling tool is designed to help: it walks you from named entities and attributes straight through to generated SQL DDL, so the naming your team agrees on in the model is the naming that ships.

There's no separate hand-off step where a name can quietly change on the way from whiteboard to migration file. Agree on the convention once, model against it, and the DDL your engineers inherit already matches what you decided.

Key Takeaways

Naming is documentation, not decoration — an ambiguous column name forces every future reader to go verify its meaning in application code instead of reading it off the schema.
Pick singular or plural tables based on your ORM's default, not on a grammatical preference, and make junction tables (user_projects) an explicit, documented exception.
Prefix every boolean with is_, has_, or can_ so a reader can tell it's a flag, not a status string, without opening any documentation.
Name foreign keys <singular_table>_id in snake_case and reserve fk_ for constraint names, not column names, to avoid camelCase case-folding surprises in Postgres and similar dialects.
Never use a bare SQL reserved word (order, user, group, table, key) as a table or column name — check your dialect's reserved list before you name anything new.
Renaming a live column is expensive — expect an expand-contract migration, not a single ALTER TABLE, which is exactly why getting the name right early is cheaper than fixing it later.
A one-page convention enforced in code review beats a long style guide nobody reads — write it down once, and check new migrations against it every time.

Frequently Asked Questions

Should database table names be singular or plural?

Either works if applied consistently — plural (users) matches Rails' and Laravel's ORM defaults, while singular (user) matches how many hand-written SQL schemas and data warehouses read once you're several joins deep. The real requirement is picking one and never mixing both in the same schema.

Is `is_active` better than `active` for a boolean column?

Yes — is_active unambiguously signals a true/false flag, while a bare adjective like active could be misread as a status string or an enum value by anyone who hasn't checked the column's actual type. The verb prefix (is_, has_, can_) costs a few extra characters and removes an entire class of ambiguity.

Should foreign key columns be named `user_id` or `userId`?

Use user_id (snake_case) in the database itself, even if your application code uses camelCase, because unquoted camelCase identifiers get folded to lowercase in PostgreSQL and several other SQL dialects, causing confusing case-mismatch errors down the line. Let your ORM translate user_id to userId at the application boundary instead.

What are the most common reserved words to avoid in table or column names?

order, user, group, table, key, and level are reserved or heavily overloaded in most major SQL dialects (ANSI SQL, PostgreSQL, MySQL, SQL Server), and using any of them bare forces every query to quote or escape the identifier. Check your specific dialect's reserved-word list before naming a new table or column, since the exact list varies.

How do we get an entire team to actually follow a naming convention?

Write the convention down as a single page — not a long style guide — and enforce it as a checklist item in pull-request review, since a rule nobody can recall under deadline pressure won't survive contact with a real sprint. Pairing the written convention with a modeling tool that carries names straight into generated schema, so there's no manual hand-off step where drift creeps in, makes it stick further.