Database Naming Standards and Conventions

Naming conventions feel like a minor stylistic choice until a schema grows past a handful of tables, at which point inconsistency becomes a genuine source of bugs and wasted time — developers guessing whether a table is called "order" or "orders," whether a column is "createdAt" or "created_at," or whether a foreign key is "customer" or "customer_id." A consistent naming standard, decided once and enforced everywhere, removes an entire category of friction and mistakes.

There is no single naming scheme that is objectively "correct" across every database engine and team, but the value comes from picking one scheme and applying it without exception, so that every table, column, and constraint in the schema can be guessed correctly without checking documentation.

Why it matters

Names in a schema last for years and are typed thousands of times. Inconsistent names, such as custId in one table and customer_id in another, cause wrong joins and constant lookups, and names that need quoting are a daily irritation. A convention applied everywhere makes a schema predictable enough to guess.

Singular vs. Plural Table Names

Some conventions name tables after the singular entity (customer, order); others use the plural, treating a table as a collection of rows (customers, orders). Plural naming is the more common convention in modern relational schemas because a table literally holds many rows of that entity, and it avoids awkward phrasing in join-table names like order_items rather than order_item_item. Whichever you choose, apply it to every table without exception — no mixing "customer" and "orders" in the same schema.

snake_case vs. camelCase, and Reserved Words

snake_case (all lowercase, words separated by underscores: created_at, unit_price) is the dominant convention for SQL identifiers because SQL itself is case-insensitive by default in most engines unless identifiers are quoted, and mixing case reliably causes cross-platform and cross-tool inconsistencies. Column and table names should also avoid reserved SQL keywords (order, user, group) as bare identifiers, since they force awkward quoting everywhere they are referenced; prefer orders, users, user_group or purchase_order instead.

Consistent Foreign Key and Primary Key Naming

A foreign key column should be named after the singular referenced entity plus _id: customer_id, product_id, department_id. A primary key is almost always simply id, which combined with the foreign key convention above means a self-explanatory join is always table_a.id = table_b.table_a_singular_id. Consistency here means anyone reading unfamiliar SQL can correctly guess join conditions without opening the schema documentation.

• Tables: plural, snake_case — customers, order_items, product_categories.
• Primary keys: id, consistently, on every table.
• Foreign keys: singular_entity_id — customer_id, product_id.
• Booleans: prefixed is_/has_ — is_active, has_discount.
• Timestamps: suffixed _at — created_at, updated_at, deleted_at.

Example

CREATE TABLE product_categories (
    id          BIGINT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    id           BIGINT PRIMARY KEY,
    category_id  BIGINT NOT NULL REFERENCES product_categories(id),
    name         VARCHAR(150) NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL,
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Anyone reading this join can predict it without documentation:
SELECT p.name, c.name AS category_name
FROM products p
JOIN product_categories c ON p.category_id = c.id
WHERE p.is_active = TRUE;

Table names are plural snake_case, the primary key is always "id", the
foreign key category_id predictably matches product_categories.id, the
boolean uses an is_ prefix, and both timestamps use the _at suffix. A
developer unfamiliar with this exact schema can still write the join
above correctly on the first try.

Exercise

The table below uses a reserved word as its name, mixed case, an abbreviation and a space, so every query has to quote the names. Rewrite the CREATE TABLE statement with consistent snake_case names: the table orders with the columns id, customer_id and ordered_at. The insert and select that follow then work as written.

Expected output: id | customer_id | ordered_at then 1 | 7 | 2026-01-15

CREATE TABLE "Order" (
  "OrderID" INTEGER PRIMARY KEY,
  "custId" INTEGER,
  "Order Date" TEXT
);

INSERT INTO orders (id, customer_id, ordered_at) VALUES (1, 7, '2026-01-15');

SELECT id, customer_id, ordered_at FROM orders;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  ordered_at TEXT
);

INSERT INTO orders (id, customer_id, ordered_at) VALUES (1, 7, '2026-01-15');

SELECT id, customer_id, ordered_at FROM orders;

Quick quiz

Interview questions