Database Naming

Tables and Columns

Use snake_case throughout. Be explicit — avoid abbreviations.

-- Good:
CREATE TABLE users (
    id                BIGSERIAL PRIMARY KEY,
    first_name        VARCHAR(100) NOT NULL,
    last_name         VARCHAR(100) NOT NULL,
    email_address     VARCHAR(255) UNIQUE NOT NULL,
    password_hash     VARCHAR(255) NOT NULL,
    is_email_verified BOOLEAN DEFAULT FALSE,
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_roles (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_name  VARCHAR(50) NOT NULL,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    granted_by BIGINT REFERENCES users(id)
);

-- Avoid — PascalCase, abbreviations, generic names:
CREATE TABLE User (
    id           BIGSERIAL PRIMARY KEY,
    fname        VARCHAR(100),
    lname        VARCHAR(100),
    email        VARCHAR(255),
    pwd_hash     VARCHAR(255),
    verified     BOOLEAN,
    created      TIMESTAMP WITH TIME ZONE,
    modified     TIMESTAMP WITH TIME ZONE
);

Index Naming

Follow a {type}_{table}_{column(s)} pattern:

Prefix	Type
idx_	Standard index
uniq_	Unique index
pk_	Primary key (often implicit)
fk_	Foreign key constraint

-- Good:
CREATE INDEX idx_users_email_address ON users(email_address);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE UNIQUE INDEX uniq_user_roles_user_role ON user_roles(user_id, role_name);

-- Avoid:
CREATE INDEX idx1 ON users(email_address);
CREATE INDEX user_idx ON users(created_at);

Conventions at a Glance

Object	Convention	Example
Tables	Plural nouns	users, orders, user_roles
Columns	Singular nouns/adjectives	email_address, created_at
Booleans	is_ or has_ prefix	is_active, has_attachment
Timestamps	_at suffix	created_at, deleted_at
Foreign keys	Referenced table + _id	user_id, order_id
Junction tables	Both table names combined	user_roles, order_items