Decision Tree

Need table name?           → UPPER_SNAKE_CASE, singular
Need column name?          → lower_snake_case
Need boolean column?       → Prefix with is_, has_, can_
Need index name?           → idx_table_columns
Need constraint name?      → pk_, fk_, uq_, ck_ prefixes

SQL Style Guide

To ensure maintainability, readability, and consistency across database projects, follow these styling and naming conventions.

1. Naming Conventions

1.1 Tables

Case: Use UPPER_SNAKE_CASE or PascalCase consistently. (Standard: UPPER_SNAKE_CASE for SQL compatibility).
Plurality: Use Singular names for entities to represent the definition of a single record (e.g., USER, ORDER, not USERS).
Prefixes: Avoid Hungarian notation (e.g., tbl_User).
Junction Tables: Combine names of related tables (e.g., USER_ROLE).

1.2 Columns

Case: Use lower_snake_case.
Clarity: Names should be descriptive (e.g., created_at instead of date).
Booleans: Prefix with is_, has_, or can_ (e.g., is_active).
Foreign Keys: Use fk_<target_table_id> or <target_table>_id consistently.

1.3 Keys & Constraints

Explicitly name all constraints to facilitate debugging.

Primary Keys: pk_<table_name>
Foreign Keys: fk_<source>_<target>
Unique: uq_<table_name>_<columns>
Check: ck_<table_name>_<condition_description>

1.4 Database Objects

Views: Prefix with v_ or vw_.
Materialized Views: Prefix with mv_.
Functions: Prefix with fn_ (e.g., fn_calculate_tax).
Procedures: Prefix with sp_ (Stored Procedure) (e.g., sp_archive_orders).
Triggers: Prefix with trg_ followed by timing (e.g., trg_users_before_insert).
Indexes: Prefix with idx_ followed by table and columns (e.g., idx_employee_organization_id).

2. Formatting

2.1 Keywords

Write all SQL keywords in UPPERCASE (e.g., SELECT, FROM, WHERE).

2.2 Indentation

Use 4 spaces for indentation.
Align clauses for readability.

SELECT
    u.id,
    u.email,
    COUNT(o.id) AS total_orders
FROM USER u
LEFT JOIN ORDER o ON u.id = o.user_id
WHERE
    u.is_active = TRUE
GROUP BY
    u.id,
    u.email;

2.3 Comments

Use -- for single-line comments.
Use /* ... */ for multi-line block comments.
Header: Every script/procedure must have a header documenting Author, Date, and Purpose.

-- =============================================================================
-- PURPOSE: Calculates monthly recurring revenue
-- AUTHOR:  [Name]
-- DATE:    YYYY-MM-DD
-- =============================================================================

3. Best Practices

No SELECT *: Always specify columns explicitly to avoid breaking changes when schemas evolve.
Aliases: Use short, meaningful aliases for tables (e.g., u for USER).
Termination: Always terminate statements with a semicolon ;.

4. Reference Implementation

Below is an example schema that demonstrates the application of these Style, Design, and Security rules.

-- =============================================================================
-- SCRIPT: Example Schema
-- PURPOSE: Demonstrates the application of custom-rules/database standards.
-- =============================================================================

-- 1. Table Creation (Style Guide: UPPER_SNAKE_CASE, Singular)
-- Design Pattern: Surrogate PK, text type usage.
CREATE TABLE ORGANIZATION (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tax_id VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Explicit Constraint Naming
    CONSTRAINT uq_organization_tax_id UNIQUE (tax_id),
    CONSTRAINT ck_organization_name_len CHECK (LENGTH(name) > 0)
);

-- 2. Child Table with Foreign Key
CREATE TABLE EMPLOYEE (
    id SERIAL PRIMARY KEY,
    organization_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    full_name TEXT NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary >= 0),
    
    -- Foreign Key with Indexing (to be added below)
    CONSTRAINT fk_employee_organization FOREIGN KEY (organization_id) REFERENCES ORGANIZATION(id)
);

-- 3. Indexing (Efficiency Pattern)
-- Index FKs
CREATE INDEX idx_employee_organization_id ON EMPLOYEE(organization_id);
-- Index Searchable fields
CREATE INDEX idx_employee_email ON EMPLOYEE(email);

-- 4. Junction Table (N:M Relationship)
CREATE TABLE PROJECT (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE EMPLOYEE_ASSIGNMENT (
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT NOW(),
    
    PRIMARY KEY (employee_id, project_id),
    CONSTRAINT fk_assignment_employee FOREIGN KEY (employee_id) REFERENCES EMPLOYEE(id),
    CONSTRAINT fk_assignment_project FOREIGN KEY (project_id) REFERENCES PROJECT(id)
);

-- 5. Stored Procedure (Logic Guidelines)
-- Header, idempotent, error handling
CREATE OR REPLACE PROCEDURE sp_register_employee(
    p_org_id INT,
    p_email VARCHAR,
    p_name VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Validation
    IF NOT EXISTS (SELECT 1 FROM ORGANIZATION WHERE id = p_org_id) THEN
        RAISE EXCEPTION 'Organization % does not exist', p_org_id;
    END IF;

    -- Insertion
    INSERT INTO EMPLOYEE (organization_id, email, full_name)
    VALUES (p_org_id, p_email, p_name);
    
    -- Transaction control is implicit in procedures if needed, or controlled by caller.
    
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Employee % already registered.', p_email;
END;
$$;

-- 6. RLS (Security Guidelines)
ALTER TABLE EMPLOYEE ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_isolation_policy ON EMPLOYEE
FOR ALL
USING (organization_id = current_setting('app.current_org_id')::INT);

style-guide