Database column names outlive the code that queries them. A name chosen carelessly on Monday becomes a name you're still using ten years later. The team has changed twice, the ORM has been rewritten, and the column name persists because changing it requires a migration on production data that nobody wants to risk.

Naming database columns well is a small upfront investment that pays for itself across the system's lifetime. Here's how to do it.

The case convention: snake_case

Use lowercase snake_case for column names. user_id, not UserId or userId or USERID. This is the dominant convention across PostgreSQL, MySQL, SQLite, and most other relational databases.

The reasons are practical:

  • Quoting becomes optional. Mixed-case identifiers in PostgreSQL require double-quoting (SELECT "UserId" FROM users), which is awkward to type and easy to forget. snake_case identifiers can be unquoted.
  • Case-folding behavior varies. PostgreSQL folds unquoted identifiers to lowercase. MySQL is case-insensitive on Windows but case-sensitive on Linux. snake_case sidesteps all of this.
  • It matches the SQL convention. SQL keywords are conventionally uppercase (SELECT, FROM); your identifiers are conventionally lowercase. The visual contrast makes queries readable.
  • Most query result columns end up snake_case anyway after passing through ORMs, frameworks, or CLI tools.

If you're starting a new database, snake_case is the safest default. Mixed conventions or PascalCase will work but create ongoing friction.

Use plural for tables, singular for columns

This is more controversial than the case rule, but the most-followed pattern in modern frameworks (Rails, Django, Laravel) is:

  • Tables are plural: users, orders, product_categories. A table holds many rows of the same kind.
  • Columns are singular: user_id, order_total, category_name. A column holds one value per row.

The alternative (singular table names like user, order) has adherents and is fine if applied consistently. The mistake is mixing them — having users, order, and products in the same schema.

Foreign keys: prefix with the referenced table's singular form

A foreign key in orders referencing users should be named user_id, not id or users_id or fk_user.

The pattern: <singular_table>_id. This makes joins read naturally:

SELECT orders.id, users.email
FROM orders
JOIN users ON orders.user_id = users.id;

Most ORMs detect this convention and wire up associations automatically. Django's ForeignKey, Rails' belongs_to, Prisma's relations all assume user_id rather than fk_user.

Avoid SQL reserved words

SQL has many reserved words that vary by database: user, order, group, type, desc, asc, where, select, from. If you use a reserved word as a column name, you'll need to escape it everywhere — with backticks in MySQL, double quotes in PostgreSQL, square brackets in SQL Server.

Common names that are reserved in at least one major database:

  • user (PostgreSQL — use users for table, fine for column)
  • order (most databases — use orders or purchase_order)
  • group (most databases)
  • type (some databases — usually OK as column)
  • key (MySQL)
  • desc (most databases — use description)
  • timestamp (some databases)
  • schema (most databases)

The fix: pluralize tables (users not user) or use a more specific name (order_total instead of order).

Boolean columns

Boolean columns should read naturally as questions or assertions. Three conventions are common:

  • is_/has_/can_ prefix: is_active, has_subscription, can_edit. Reads as a yes/no question.
  • _at suffix for timestamps that double as booleans: deleted_at (NULL means not deleted), verified_at, archived_at. The presence of a timestamp signals truth; this is more flexible than a boolean because it also tells you when.
  • Past participle: active, verified, archived. Reads as a state.

Pick one convention per schema. Avoid mixing is_active and active in the same database — they read like they mean different things.

Timestamps

Timestamps follow a strong convention: end the column name in _at for instants and _on for dates.

  • created_at (when the row was created — datetime)
  • updated_at (when the row was last updated — datetime)
  • deleted_at (soft-delete marker — datetime)
  • published_at, scheduled_at, completed_at — domain timestamps
  • birth_date or date_of_birth (a date with no time component)
  • start_date, end_date — date ranges

The created_at and updated_at pair is so universal that most ORMs maintain them automatically. Adding them to every table is a near-free productivity win.

Money

Money columns should include the unit in the name when there's any ambiguity. Three patterns work:

  • Cents-based integer: price_cents stores 1999 for $19.99. Avoids floating-point rounding. Common in payment systems.
  • Decimal with currency separate: amount NUMERIC(12,2) + currency CHAR(3) stores 19.99 + 'USD'. Suitable for multi-currency systems.
  • BigInt with currency separate: similar to cents-based but with currency column for multi-currency.

What you should never have: a column called price whose unit is ambiguous. Is it dollars? Cents? Is it the price before or after tax?

Avoid abbreviations unless they're truly canonical

Abbreviations save typing once and cost reading every subsequent query. The exceptions are abbreviations so universal they read as words:

  • id (universal)
  • url (universal)
  • ip (universal)
  • http_status (universal in web contexts)
  • iso_code (when referring to ISO standards)

What to avoid:

  • usr_nm instead of user_name
  • cust_addr instead of customer_address
  • tmstmp instead of created_at

The query is read more often than it's written. Pay the typing cost.

Use consistent naming for similar concepts

If one table has user_id as the foreign key to the users table, every table should use user_id for that purpose. Don't have user_id in one table and owner_id in another if they both reference the same users table — unless the second one really means something semantically different (in which case owner_id is fine).

The principle: same concept, same name. Different concept, different name. Inconsistency reads as ambiguity.

Index naming convention

Most databases auto-generate index names if you don't specify them. The default names are usually fine. If you do name them explicitly, the convention is idx_<table>_<column(s)>:

  • idx_users_email
  • idx_orders_user_id_created_at
  • idx_products_sku_unique (for unique indexes)

This makes index names searchable and self-documenting.

Common anti-patterns to avoid

"data" or "info" suffix: user_data, order_info. These columns usually become JSON blobs that hold everything you didn't model properly. If the data inside has structure, model it as columns. If it's genuinely freeform, name the column for what it holds (preferences, metadata) not for the generic "data."

Hungarian notation: strName, intAge, boolActive. The database knows the type; you don't need to encode it in the column name.

Suffixing with the table name: user_name in the users table where users.user_name is redundant. Just name is enough, since the table prefix is already implied. Reserve table-prefixed names for foreign keys.

Cryptic abbreviations: typ, stat, cd, nm. Just write type, status, code, name.

Numbered columns: address_1, address_2, address_3. This is almost always a sign you should have a separate addresses table with a foreign key.

Converting from other conventions

If you're migrating from a system that used PascalCase or camelCase column names — say, you inherited an old C# / SQL Server schema and you're moving to PostgreSQL — our bulk converter can rename a list of columns to snake_case quickly. Paste the column list, switch to snake_case mode, and copy the output. Combine with a generated ALTER TABLE ... RENAME COLUMN script for the actual migration.

For one-off naming questions, the snake_case converter is the right tool.

The bigger principle

Database schemas are public APIs. Other systems query them. Future developers read them. The names you pick define how everyone else thinks about the data.

Aim for names that read like English nouns. Avoid anything that requires explanation in a Slack message. The naming work is small, slow, and easy to skip — but the difference between a well-named schema and a poorly-named one shows up in every query, every report, every onboarding session for the next decade.