# Consistent naming convention across tables and columns

- **Pattern:** `ab-000902` (`database-design-operations.schema-design.naming-conventions`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000902
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000902)

## Why it matters

Inconsistent naming conventions are a signature of AI-generated schemas assembled across multiple sessions without coherence review. A schema mixing `userId`, `post_id`, `CreatedAt`, and `updated_at` forces developers to look up column names for every query, breaks ORM mappings that assume a consistent convention, and produces mapping errors when tools like Prisma generate TypeScript types. ISO 25010 maintainability-readability captures the downstream cost: every new developer must reverse-engineer the implicit rules before they can write correct queries. Naming inconsistencies also cause real bugs when case-sensitive column lookups fail in production queries.

## Severity rationale

Low because naming inconsistencies degrade developer productivity and maintainability without directly causing runtime failures or data loss in most ORM setups.

## Remediation

Pick one convention — PostgreSQL standard is `snake_case` with plural table names — and apply it via `RENAME COLUMN` or ORM `@map` directives. For Prisma codebases that want camelCase TypeScript but snake_case SQL, use `@map` and `@@map`.

```prisma
model User {
  id        String   @id
  createdAt DateTime @map("created_at")
  @@map("users")
}
```

```sql
-- Rename inconsistent columns
ALTER TABLE posts RENAME COLUMN "postTitle" TO title;
ALTER TABLE posts RENAME COLUMN "CreatedAt" TO created_at;
```

For existing applications with ORM-mapped models, prefer `@map` over database renames to avoid breaking existing queries until you can migrate them all at once.

## Detection

- **ID:** `naming-conventions`
- **Severity:** `low`
- **What to look for:** List all table names and column names in the schema. For each, classify the naming convention used (snake_case, camelCase, PascalCase). Check for a single consistent convention: `snake_case` (PostgreSQL convention — `user_id`, `created_at`, `order_items`), `camelCase` (JavaScript-influenced — `userId`, `createdAt`, `orderItems`), or `PascalCase` (C#/ORM-influenced — `UserId`, `CreatedAt`). Mixed conventions are a sign of schema generated by multiple AI prompts or sessions without coherence. Also check: (1) Singular vs. plural table names — should be consistent (either always `user`/`order` or always `users`/`orders`). (2) ID column naming — `id` (most common) vs. `table_id` (e.g., `user_id` as the PK of the users table, which is confusing). (3) Timestamp naming — `created_at`/`updated_at` vs. `createdAt`/`updatedAt` — should be consistent across all tables.
- **Pass criteria:** At least 90% of tables follow one consistent naming convention (snake_case or camelCase). Table naming (plural or singular) is consistent across all tables. Timestamp columns use one naming pattern (`created_at` everywhere or `createdAt` everywhere). ID column naming is consistent.
- **Fail criteria:** Mix of snake_case and camelCase tables or columns in the same schema. Mix of plural and singular table names (e.g., `users`, `order`, `ProductCategory`). Timestamps named inconsistently (`created_at` in some tables, `createdAt` in others, `date_created` in another).
- **Skip (N/A) when:** N/A — always applicable for any project with a database.
- **Detail on fail:** Specify the inconsistency. Example: `"Schema mixes snake_case and camelCase: 'user_id', 'postTitle', 'created_at', 'updatedAt' found in same schema."` or `"Table naming is inconsistent: 'users', 'order', 'Products', 'tag' — mix of plural, singular, and casing conventions."`.
- **Remediation:** Pick one convention and apply it everywhere. For PostgreSQL, `snake_case` with plural table names is the standard:

  ```sql
  -- Standard Postgres naming convention
  CREATE TABLE users (          -- plural, snake_case
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );

  CREATE TABLE orders (         -- plural, snake_case
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    total      DECIMAL(12, 2) NOT NULL,
    status     TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );
  ```

  If using Prisma with a camelCase TypeScript codebase, use `@map` to keep the DB in snake_case while TypeScript models use camelCase:
  ```prisma
  model User {
    id        String   @id @map("id")
    createdAt DateTime @map("created_at")
    @@map("users")
  }
  ```

---

## External references

- iso-25010 maintainability.readability

Taxons: code-quality

HTML version: https://auditbuffet.com/patterns/ab-000902