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);