Explanation

SQL Table Naming Dilemma: Singular vs. Plural

Adela
Adela3 min read
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: orderorder_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)

ApproachCommon InKey Advantage
SingularEnterprise / traditional systemsOOP consistency; entity focus
PluralWeb frameworks / modern stacksReadability; framework defaults

There’s no universal standard; both conventions are widespread. What matters is a deliberate choice and consistency.

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_caseusers, orders, order_items, audit_logs.
  2. Join tables: plural + plural, alphabetical → orders_products, roles_users.
  3. Columns: singular, snake_caseid, 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.