Database Guidelines

Field Details
Status Active
Last Updated 05-11-2026

Purpose

To ensure efficient, scalable, and maintainable database design and usage across all services

Scope

Applies to: All relational databases (PostgreSQL, MySQL), NoSQL databases

Does not apply to: In-memory caches, third-party databases

Table Design

Naming Conventions

  • Tables: snake_case, plural
  • Columns: snake_case
  • Primary key: always id
  • Foreign keys: <table>_id
  • Timestamps: created_at, updated_at
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE user_orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    order_date TIMESTAMP
);

Indexes

When to Add Indexes

  • Foreign keys (automatically indexed in most databases)
  • Columns used in WHERE clauses frequently
  • Columns used in JOIN conditions
  • Columns used in ORDER BY
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id_created ON orders(user_id, created_at);

Index Naming

Format: idx_<table>_<columns>

Migrations

Migration File Naming

Format: <timestamp>_<description>.sql

20240101120000_create_users_table.sql
20240102093000_add_email_to_users.sql

Migration Rules

  • Every migration must be reversible
  • Never modify existing migrations
  • Test migrations on a copy of production data
  • Keep migrations small and focused
-- UP Migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- DOWN Migration  
ALTER TABLE users DROP COLUMN phone;

Query Optimization

Common Rules

  • Avoid SELECT * - specify columns explicitly
  • Use LIMIT for large result sets
  • Avoid N+1 queries - use joins or batch loading
  • Use EXPLAIN to analyze slow queries
SELECT id, name, email FROM users WHERE status = 'active' LIMIT 100;

-- Instead of N+1 queries:
SELECT u.*, o.* FROM users u 
   LEFT JOIN orders o ON u.id = o.user_id 
   WHERE u.id IN (1,2,3);

Data Integrity

Constraints

  • Always use NOT NULL where applicable
  • Add UNIQUE constraints for natural keys
  • Use CHECK constraints for business rules
  • Define foreign key relationships
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10,2) CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0
);

Soft Deletes

  • Use deleted_at timestamp column instead of hard deletes
  • Add partial indexes to exclude soft-deleted records
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- Partial index for performance
CREATE INDEX idx_active_users ON users(email) 
   WHERE deleted_at IS NULL;

Connection Pooling

  • Set appropriate pool size (typically 10-20 connections per service)
  • Always close connections properly
  • Use connection timeout settings
Pool Size: 20
Connection Timeout: 30 seconds
Idle Timeout: 10 minutes

Exceptions

NoSQL databases may follow different conventions based on the specific database type

Changelog

Version Date Author Change
1.0.0 05-11-2026 Tibin Sunny Initial version