SQL Table Naming Dilemma: Singular vs. Plural

TL;DR: Pick one style, document it, and stick to it. If you’re undecided, plural tables (users, orders) are a safe default: they read naturally in SQL and dodge some reserved words. Consistency matters more than the “right” choice.

Why it matters#

Names ripple through SQL, ORMs, migrations, BI, and docs. A consistent convention lowers cognitive load and onboarding time.

The two camps#

Singular (user, order_item)#

Pros

• OOP alignment: maps cleanly to classes/entities (a row is a User).
• Language simplicity: avoids irregular plurals for international teams.
• Master–detail reads cleanly: order ↔ order_detail can feel natural.

Cons

• Reserved words: user, order, group, session can clash.
• Query feel: SELECT * FROM user reads a bit stilted.

Plural (users, order_items)#

Pros

• Natural language in SQL: SELECT * FROM users WHERE age > 21.
• Reserved-word avoidance: orders > order.
• Framework affinity: popular in Rails and many modern stacks.

Cons

• Semantic mismatch: a row is a user but lives in users.
• Irregulars: person/people, child/children add edge cases.

Industry split (rule of thumb)#

Quick decision guide#

• Greenfield & undecided: choose plural tables.
• Existing codebase: match what’s there—consistency beats preference.
• Reserved-word risk: prefer plural (orders, groups).
• Strict DDD shops / heavy OOP mapping: singular can fit better.

Practical conventions (copy-paste)#

1. Tables: plural, snake_case → users, orders, order_items, audit_logs.
2. Join tables: plural + plural, alphabetical → orders_products, roles_users.
3. Columns: singular, snake_case → id, user_id, created_at, updated_at.
4. Views / MVs: prefix + plural → v_active_users, mv_daily_signups.
5. Reference tables: plural → countries, currencies, order_statuses.
6. Irregular nouns: standardize once (e.g., always people or persons) and stick to it.

Master–detail naming tips#

• Singular world: order / order_detail is tidy and readable.
• Plural world (most common): orders / order_items keeps the collection metaphor; avoid orders_details. Use the object’s name (order_items) rather than “detail(s)”.

Governance (make it stick)#

• Write the rule in your engineering handbook.
• Add a schema linter/check in CI to block drift.
• Provide examples for tricky cases (irregular plurals, join tables, reserved words).

Examples#

Plural (recommended default)

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)
);

Singular (for strict entity alignment)

CREATE TABLE user (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE order (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES user(id)
);
/* Beware: user/order can conflict with reserved words in some contexts */

Final recommendation#

Choose one convention, document it, enforce it. If you don’t have strong reasons, pick plural tables + singular columns with snake_case, and don’t revisit the debate in every PR. Consistency > perfection.