Database Naming Conventions: snake_case, PascalCase & Best Practices

By WordCaseFix · 8 min read · Databases

Database names live forever. A column name added in haste during a Friday afternoon deploy becomes the name your team grepped, queried, and joined against for the next ten years. Renaming it later means a migration, careful coordination, and downtime risk. That makes naming conventions one of the highest-leverage decisions in schema design. This guide covers the practical rules that survive contact with production.

Snake Case Converter Convert any identifier to clean, database-friendly snake_case.

Try Snake Case Converter →

snake_case Is the De Facto Standard

The overwhelming majority of SQL databases — PostgreSQL, MySQL, SQLite, Oracle, and most ORMs (Django, Rails, SQLAlchemy, Hibernate's default naming strategy) — use snake_case for table and column names. There are two reasons.

First, SQL identifiers are case-insensitive in most engines but case-folded inconsistently. PostgreSQL folds unquoted identifiers to lowercase. SQL Server preserves them but compares case-insensitively by default. MySQL behavior depends on the operating system. snake_case sidesteps all of this because all letters are already lowercase.

Second, snake_case visually separates words without requiring case sensitivity. customer_account_holder is unambiguous; customeraccountholder is unreadable; CustomerAccountHolder requires quoting in PostgreSQL.

Same schema, three styles:

snake_case (recommended):
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT);

PascalCase (requires quoting in Postgres):
CREATE TABLE "OrderItems" ("OrderId" INT, "ProductId" INT);

camelCase (silently folded to lowercase in Postgres):
CREATE TABLE orderItems (orderId INT); -- becomes "orderitems"!

Tables: Plural or Singular?

The debate between plural (users) and singular (user) table names is one of the oldest in database design. Both have merit.

Plural names match the natural-language intuition: a table called users contains many users. Rails and most modern web frameworks default to plural.

Singular names emphasize that each row represents one entity, and they keep join syntax cleaner: user.id = order.user_id reads more naturally than users.id = orders.user_id. The classic Microsoft and Oracle data-modeling tradition is singular.

Either choice is defensible. The non-negotiable rule is consistency — apply the same style to every table in the schema.

Column Naming Rules

Primary keys: just id. Don't prefix with the table name (user_id inside the users table is redundant). Some teams disagree and prefer fully prefixed PKs to make joins more readable — pick one rule.
Foreign keys: <referenced_table_singular>_id. A foreign key from orders to users should be user_id. This pattern lets natural joins work (USING (user_id)).
Booleans: prefix with is_ or has_. is_active, has_paid, is_deleted. Avoid bare adjectives like active which read ambiguously.
Timestamps: suffix with _at. created_at, updated_at, published_at, deleted_at. Use _on for date-only fields (birth_on).
Counters: suffix with _count. comment_count, view_count.
Currency: include the currency code or use a separate column. price_cents with implicit USD, or price_amount + price_currency.

Naming Indexes, Constraints, and Triggers

Most databases will auto-generate ugly names like fk_a8c3b9d_idx if you let them. Explicit names make migrations and troubleshooting far easier. Follow a predictable pattern.

Object	Pattern	Example
Primary key	pk_<table>	pk_users
Foreign key	fk_<table>_<col>	fk_orders_user_id
Unique constraint	uq_<table>_<col>	uq_users_email
Index	ix_<table>_<col>	ix_orders_created_at
Composite index	ix_<table>_<c1>_<c2>	ix_orders_user_id_created_at
Check constraint	ck_<table>_<col>	ck_orders_quantity_positive
Trigger	tr_<table>_<action>	tr_orders_set_updated_at

Case Converter Switch identifiers between snake_case, PascalCase, and camelCase.

Try Case Converter →

Reserved Words to Avoid

Every SQL engine has a list of reserved words that cannot be used as bare identifiers. Common ones: user, order, group, type, name, date, time, level, status. PostgreSQL will let you use most of them with quoting; MySQL is stricter. Avoid them entirely. Use users instead of user, orders instead of order, order_type instead of type.

Junction (Join) Tables

For many-to-many relationships, name the junction table by concatenating both table names in alphabetical order, separated by an underscore: posts_tags, users_roles. Some teams prefer a more descriptive name when the relationship has meaning beyond a simple link: memberships instead of users_groups.

Schema and Database Names

Use lowercase with underscores or no separator at all for database names: billing, analytics, app_production. Avoid hyphens — they require quoting in nearly every engine. Schemas (PostgreSQL) follow the same rule.

How This Plays with Your Application Code

If your backend is Python, Ruby, or Go, snake_case database columns map directly to native identifiers — no translation needed. If your backend is Java, C#, or TypeScript, your ORM will convert snake_case columns to camelCase fields automatically (Hibernate's snake_case strategy, EF Core's naming conventions, Prisma's @map attributes). The database stays canonical; only the language layer changes. See our JSON key naming guide for advice on the wire format.

      Golden rule: The database is the canonical source of truth. Use snake_case in the schema, even if your application code uses camelCase. Translation belongs in the ORM layer, not in the column name.
    

Frequently Asked Questions

Can I use camelCase columns in PostgreSQL?

Technically yes, but only if you double-quote them in every query (SELECT "userId" FROM users). Unquoted, PostgreSQL folds them to lowercase. snake_case avoids this trap entirely.

Should foreign keys be indexed automatically?

PostgreSQL does not create them automatically; MySQL InnoDB does. Always add explicit indexes on foreign keys regardless of engine. Naming them with the ix_ prefix makes them findable.

Is it OK to use plural table names with singular foreign keys?

Yes, this is the most common pattern: users table, user_id foreign key column. The mismatch matches how English works ("the user's id").