Consistent naming convention across tables and columns

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.

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.

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

-- 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.

• ID: database-design-operations.schema-design.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:

  -- 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:

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