Docs For AI
Design

Best Practices Checklist

Comprehensive checklist for reviewing database design against industry best practices

Best Practices Checklist

This comprehensive checklist helps you evaluate database design against industry best practices. Use it as a systematic review guide for new designs or existing databases.

Quick Assessment Checklist

Database Design Review Checklist:
┌─────────────────────────────────────────────────────────────┐
│  □ Naming Conventions                    Score: __/25       │
│  □ Schema Design                         Score: __/25       │
│  □ Performance Considerations            Score: __/25       │
│  □ Security & Maintenance               Score: __/25       │
│  ─────────────────────────────────────────────────────────  │
│  TOTAL SCORE:                                    __/100     │
└─────────────────────────────────────────────────────────────┘

1. Naming Conventions (25 points)

Table Naming

CheckPointsPass/Fail
Use plural nouns for table names (users, not user)2□
Use snake_case consistently2□
Avoid reserved SQL keywords2□
Names are descriptive and self-documenting2□
No prefixes like tbl_ or table_2□

Verification Query:

-- Check table naming patterns
SELECT
    table_name,
    CASE
        WHEN table_name ~ '^[a-z][a-z0-9_]*[s]$' THEN 'Good: plural snake_case'
        WHEN table_name ~ '^tbl_' THEN 'Bad: tbl_ prefix'
        WHEN table_name ~ '[A-Z]' THEN 'Bad: contains uppercase'
        WHEN table_name !~ 's$' THEN 'Warning: might not be plural'
        ELSE 'Review manually'
    END as naming_assessment
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE'
ORDER BY table_name;

Column Naming

CheckPointsPass/Fail
Use snake_case for column names2□
Primary key named id or {table}_id2□
Foreign keys named {referenced_table}_id2□
Boolean columns prefixed with is_, has_, can_2□
Timestamp columns: created_at, updated_at, deleted_at2□
Avoid abbreviations (use description, not desc)1□

Good vs Bad Naming Examples:

-- Bad naming
CREATE TABLE tblUser (
    UserID INT,
    fName VARCHAR(50),
    LName VARCHAR(50),
    isActive INT,  -- Should be BOOLEAN
    create_date TIMESTAMP
);

-- Good naming
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Index and Constraint Naming

CheckPointsPass/Fail
Index: idx_{table}_{columns}2□
Foreign Key: fk_{table}_{referenced_table}1□
Unique: uq_{table}_{columns}1□
Check: chk_{table}_{description}1□
-- Check constraint naming patterns
SELECT
    constraint_name,
    constraint_type,
    table_name,
    CASE
        WHEN constraint_type = 'PRIMARY KEY' AND constraint_name LIKE '%_pkey' THEN 'OK'
        WHEN constraint_type = 'FOREIGN KEY' AND constraint_name LIKE 'fk_%' THEN 'OK'
        WHEN constraint_type = 'UNIQUE' AND constraint_name LIKE 'uq_%' THEN 'OK'
        WHEN constraint_type = 'CHECK' AND constraint_name LIKE 'chk_%' THEN 'OK'
        ELSE 'Review naming'
    END as naming_check
FROM information_schema.table_constraints
WHERE table_schema = 'public'
ORDER BY table_name, constraint_type;

2. Schema Design (25 points)

Primary Keys

CheckPointsPass/Fail
Every table has a primary key5□
PKs are immutable (never updated)2□
Appropriate PK type (SERIAL/UUID/BIGINT)2□
No composite PKs unless necessary1□

Foreign Keys & Relationships

CheckPointsPass/Fail
All relationships have FK constraints3□
Appropriate ON DELETE actions defined2□
No circular dependencies2□
Junction tables for many-to-many2□

Check Relationship Coverage:

-- Find _id columns without FK constraints
SELECT
    c.table_name,
    c.column_name
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND c.column_name LIKE '%_id'
AND c.column_name != 'id'
AND NOT EXISTS (
    SELECT 1
    FROM information_schema.key_column_usage kcu
    JOIN information_schema.table_constraints tc
        ON kcu.constraint_name = tc.constraint_name
    WHERE kcu.table_name = c.table_name
    AND kcu.column_name = c.column_name
    AND tc.constraint_type = 'FOREIGN KEY'
)
ORDER BY c.table_name;

Data Types

CheckPointsPass/Fail
Appropriate data types for each column2□
VARCHAR lengths are reasonable1□
Using ENUM for fixed value sets1□
Timestamps use TIMESTAMPTZ1□
Money uses DECIMAL/NUMERIC, not FLOAT1□

Data Type Recommendations:

DataRecommended TypeAvoid
CurrencyDECIMAL(19,4)FLOAT, REAL
EmailVARCHAR(255)TEXT
UUIDUUIDVARCHAR(36)
BooleanBOOLEANINT, CHAR(1)
TimestampTIMESTAMPTZTIMESTAMP
JSON dataJSONBTEXT, JSON
IP AddressINETVARCHAR

3. Performance Considerations (25 points)

Indexing Strategy

CheckPointsPass/Fail
PKs are indexed (automatic)2□
FKs have indexes3□
Frequently queried columns indexed3□
No redundant/duplicate indexes2□
Composite indexes follow query patterns2□

Check FK Index Coverage:

-- Foreign keys without indexes
SELECT
    tc.table_name,
    kcu.column_name as fk_column,
    'Missing Index' as status
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = 'public'
AND NOT EXISTS (
    SELECT 1
    FROM pg_indexes pi
    WHERE pi.tablename = tc.table_name
    AND pi.indexdef LIKE '%' || kcu.column_name || '%'
);

Query Optimization

CheckPointsPass/Fail
No SELECT * in production code2□
Pagination implemented properly2□
N+1 queries identified and fixed3□
Complex queries have covering indexes2□

Table Structure

CheckPointsPass/Fail
Large TEXT/BLOB columns in separate table2□
Appropriate partitioning for large tables2□

4. Security & Maintenance (25 points)

Security

CheckPointsPass/Fail
Sensitive data encrypted at rest3□
No passwords stored in plain text3□
Least privilege access controls2□
Audit columns present (created_at, updated_at)2□
Soft delete implemented where needed2□

Verify Audit Columns:

-- Check for audit columns
SELECT
    t.table_name,
    BOOL_OR(c.column_name = 'created_at') as has_created_at,
    BOOL_OR(c.column_name = 'updated_at') as has_updated_at,
    BOOL_OR(c.column_name = 'deleted_at') as has_deleted_at
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
    ON t.table_name = c.table_name
WHERE t.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name
ORDER BY t.table_name;

Maintainability

CheckPointsPass/Fail
Database schema is documented3□
Migration scripts are versioned2□
Seed data for development1□
Backup strategy defined2□
Monitoring alerts configured2□

Default Values & Constraints

CheckPointsPass/Fail
Appropriate DEFAULT values set2□
NOT NULL on required columns1□
-- Columns that might need defaults
SELECT
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_default IS NULL
AND is_nullable = 'NO'
AND column_name NOT IN ('id')
ORDER BY table_name, column_name;

Complete Assessment Script

-- Comprehensive Best Practices Assessment
DO $$
DECLARE
    v_count INTEGER;
    v_total INTEGER;
    v_score INTEGER := 0;
BEGIN
    RAISE NOTICE '╔══════════════════════════════════════════════════════════╗';
    RAISE NOTICE '║        DATABASE BEST PRACTICES ASSESSMENT                ║';
    RAISE NOTICE '╚══════════════════════════════════════════════════════════╝';

    -- 1. NAMING CONVENTIONS
    RAISE NOTICE '';
    RAISE NOTICE '1. NAMING CONVENTIONS';
    RAISE NOTICE '─────────────────────';

    -- Snake case tables
    SELECT COUNT(*) INTO v_total FROM information_schema.tables
    WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

    SELECT COUNT(*) INTO v_count FROM information_schema.tables
    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    AND table_name ~ '^[a-z][a-z0-9_]*$';

    RAISE NOTICE '   Snake case tables: %/%', v_count, v_total;
    IF v_count = v_total THEN v_score := v_score + 5; END IF;

    -- 2. SCHEMA DESIGN
    RAISE NOTICE '';
    RAISE NOTICE '2. SCHEMA DESIGN';
    RAISE NOTICE '────────────────';

    -- Tables with PK
    SELECT COUNT(*) INTO v_count FROM information_schema.tables t
    WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE'
    AND EXISTS (
        SELECT 1 FROM information_schema.table_constraints tc
        WHERE tc.table_name = t.table_name
        AND tc.constraint_type = 'PRIMARY KEY'
    );
    RAISE NOTICE '   Tables with PK: %/%', v_count, v_total;
    IF v_count = v_total THEN v_score := v_score + 10; END IF;

    -- FK count
    SELECT COUNT(*) INTO v_count FROM information_schema.table_constraints
    WHERE constraint_type = 'FOREIGN KEY' AND table_schema = 'public';
    RAISE NOTICE '   Foreign Keys defined: %', v_count;
    IF v_count > 0 THEN v_score := v_score + 5; END IF;

    -- 3. PERFORMANCE
    RAISE NOTICE '';
    RAISE NOTICE '3. PERFORMANCE';
    RAISE NOTICE '──────────────';

    SELECT COUNT(*) INTO v_count FROM pg_indexes WHERE schemaname = 'public';
    RAISE NOTICE '   Total Indexes: %', v_count;
    IF v_count >= v_total THEN v_score := v_score + 5; END IF;

    -- 4. SECURITY & MAINTENANCE
    RAISE NOTICE '';
    RAISE NOTICE '4. SECURITY & MAINTENANCE';
    RAISE NOTICE '─────────────────────────';

    -- Audit columns
    SELECT COUNT(DISTINCT table_name) INTO v_count
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND column_name IN ('created_at', 'updated_at');
    RAISE NOTICE '   Tables with audit columns: %/%', v_count, v_total;
    IF v_count = v_total THEN v_score := v_score + 5; END IF;

    RAISE NOTICE '';
    RAISE NOTICE '══════════════════════════════════════════════════════════';
    RAISE NOTICE 'PRELIMINARY SCORE: %/30 (simplified assessment)', v_score;
    RAISE NOTICE '══════════════════════════════════════════════════════════';
END $$;

Checklist Summary Card

┌─────────────────────────────────────────────────────────────┐
│              DATABASE DESIGN CHECKLIST                       │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  NAMING (25 pts)                                            │
│  □ Snake_case for tables and columns                        │
│  □ Plural table names (users, orders)                       │
│  □ Descriptive, no abbreviations                            │
│  □ Standard FK naming ({table}_id)                          │
│  □ Boolean prefix (is_, has_, can_)                         │
│                                                             │
│  SCHEMA (25 pts)                                            │
│  □ Every table has primary key                              │
│  □ All relationships have foreign keys                      │
│  □ Appropriate data types                                   │
│  □ NOT NULL on required fields                              │
│  □ CHECK constraints for validation                         │
│                                                             │
│  PERFORMANCE (25 pts)                                       │
│  □ Indexes on foreign keys                                  │
│  □ Indexes on frequently queried columns                    │
│  □ No redundant indexes                                     │
│  □ Appropriate use of composite indexes                     │
│  □ Large data in separate tables                            │
│                                                             │
│  SECURITY & OPS (25 pts)                                    │
│  □ Sensitive data encrypted                                 │
│  □ Audit columns (created_at, updated_at)                   │
│  □ Soft delete where appropriate                            │
│  □ Schema documented                                        │
│  □ Migrations versioned                                     │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│  SCORE: ___/100                                             │
│  90-100: Excellent | 70-89: Good | 50-69: Fair | <50: Poor  │
└─────────────────────────────────────────────────────────────┘

Common Anti-Patterns to Avoid

Anti-PatternProblemSolution
God TableOne table with 50+ columnsSplit into related tables
EAV ModelEntity-Attribute-Value everywhereUse proper columns/JSONB
No ConstraintsData integrity relies on appAdd DB-level constraints
Stringly TypedEverything is VARCHARUse proper data types
Magic NumbersStatus codes without meaningUse ENUM or lookup table
No Audit TrailCan't track changesAdd audit columns/triggers
Implicit SchemaNo documentationDocument all tables/columns

Data Integrity

Methods to verify and ensure data correctness, consistency, and reliability

Database Indexing

Database index principles, types, and optimization strategies

On this page

Best Practices ChecklistQuick Assessment Checklist1. Naming Conventions (25 points)Table NamingColumn NamingIndex and Constraint Naming2. Schema Design (25 points)Primary KeysForeign Keys & RelationshipsData Types3. Performance Considerations (25 points)Indexing StrategyQuery OptimizationTable Structure4. Security & Maintenance (25 points)SecurityMaintainabilityDefault Values & ConstraintsComplete Assessment ScriptChecklist Summary CardCommon Anti-Patterns to Avoid