
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)
Approach | Common In | Key Advantage |
---|---|---|
Singular | Enterprise / traditional systems | OOP consistency; entity focus |
Plural | Web frameworks / modern stacks | Readability; 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)
- Tables: plural,
snake_case
→users
,orders
,order_items
,audit_logs
. - Join tables: plural + plural, alphabetical →
orders_products
,roles_users
. - Columns: singular,
snake_case
→id
,user_id
,created_at
,updated_at
. - Views / MVs: prefix + plural →
v_active_users
,mv_daily_signups
. - Reference tables: plural →
countries
,currencies
,order_statuses
. - Irregular nouns: standardize once (e.g., always
people
orpersons
) 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; avoidorders_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.