Database Architecture - MySQL

Overview

This document outlines the database design, schema standards, optimization strategies, and best practices for our SaaS CRM MySQL database.

Database Configuration

MySQL Settings

# my.cnf configuration
[mysqld]
# Basic Settings
port = 3306
bind-address = 0.0.0.0
max_connections = 500
max_allowed_packet = 64M

# InnoDB Settings
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2

# Query Cache
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Schema Design

Naming Conventions

-- Tables: plural, snake_case
customers, orders, order_items

-- Columns: snake_case
customer_id, created_at, total_amount

-- Primary keys: id
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- Foreign keys: table_singular_id
customer_id, order_id, product_id

-- Indexes: idx_table_columns
idx_customers_email, idx_orders_customer_status

-- Constraints: fk_table_reference
fk_orders_customer, fk_order_items_order

Core Tables

Users Table

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'manager', 'user') DEFAULT 'user',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_verified_at TIMESTAMP NULL,
    remember_token VARCHAR(100) NULL,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_users_email (email),
    INDEX idx_users_role_status (role, status),
    INDEX idx_users_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Customers Table

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(50) UNIQUE,
    company_name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    website VARCHAR(255),
    industry VARCHAR(100),
    employee_count INT UNSIGNED,
    annual_revenue DECIMAL(15,2),
    status ENUM('prospect', 'active', 'inactive', 'churned') DEFAULT 'prospect',
    acquisition_date DATE,
    assigned_to INT UNSIGNED,
    source VARCHAR(100),
    notes TEXT,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_customers_status (status),
    INDEX idx_customers_assigned (assigned_to),
    INDEX idx_customers_email (email),
    INDEX idx_customers_company (company_name),
    FOREIGN KEY fk_customers_assigned (assigned_to) REFERENCES users(id),
    FOREIGN KEY fk_customers_created (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Contacts Table

CREATE TABLE contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    mobile VARCHAR(50),
    job_title VARCHAR(100),
    department VARCHAR(100),
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_contacts_customer (customer_id),
    INDEX idx_contacts_email (email),
    INDEX idx_contacts_name (last_name, first_name),
    FOREIGN KEY fk_contacts_customer (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Opportunities Table

CREATE TABLE opportunities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    opportunity_number VARCHAR(50) UNIQUE,
    customer_id INT UNSIGNED NOT NULL,
    contact_id INT UNSIGNED,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    stage ENUM('prospecting', 'qualification', 'proposal', 'negotiation', 'closed_won', 'closed_lost') DEFAULT 'prospecting',
    probability TINYINT UNSIGNED DEFAULT 0,
    amount DECIMAL(15,2),
    expected_close_date DATE,
    actual_close_date DATE,
    assigned_to INT UNSIGNED NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    lost_reason VARCHAR(255),
    competitor VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_opportunities_customer (customer_id),
    INDEX idx_opportunities_stage (stage),
    INDEX idx_opportunities_assigned (assigned_to),
    INDEX idx_opportunities_close_date (expected_close_date),
    FOREIGN KEY fk_opportunities_customer (customer_id) REFERENCES customers(id),
    FOREIGN KEY fk_opportunities_contact (contact_id) REFERENCES contacts(id),
    FOREIGN KEY fk_opportunities_assigned (assigned_to) REFERENCES users(id),
    FOREIGN KEY fk_opportunities_created (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Relationship Tables

Activities Table

CREATE TABLE activities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type ENUM('call', 'email', 'meeting', 'task', 'note') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    customer_id INT UNSIGNED,
    contact_id INT UNSIGNED,
    opportunity_id INT UNSIGNED,
    assigned_to INT UNSIGNED NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    due_date DATETIME,
    completed_at TIMESTAMP NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_activities_type_status (type, status),
    INDEX idx_activities_customer (customer_id),
    INDEX idx_activities_assigned (assigned_to),
    INDEX idx_activities_due_date (due_date),
    FOREIGN KEY fk_activities_customer (customer_id) REFERENCES customers(id),
    FOREIGN KEY fk_activities_contact (contact_id) REFERENCES contacts(id),
    FOREIGN KEY fk_activities_opportunity (opportunity_id) REFERENCES opportunities(id),
    FOREIGN KEY fk_activities_assigned (assigned_to) REFERENCES users(id),
    FOREIGN KEY fk_activities_created (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Indexing Strategy

Index Types

-- Primary Key Index (automatic)
PRIMARY KEY (id)

-- Unique Index
UNIQUE INDEX idx_users_email (email)

-- Composite Index (column order matters)
INDEX idx_orders_customer_status (customer_id, status)

-- Covering Index (includes all needed columns)
INDEX idx_customers_search (company_name, email, phone)

-- Partial Index (prefix for text columns)
INDEX idx_customers_notes (notes(100))

-- Full-text Index
FULLTEXT INDEX idx_customers_fulltext (company_name, notes)

Index Guidelines

-- Analyze query patterns
EXPLAIN SELECT * FROM customers WHERE status = 'active' AND assigned_to = 123;

-- Check index usage
SHOW INDEX FROM customers;

-- Monitor slow queries
SELECT * FROM mysql.slow_log WHERE query_time > 2;

-- Index cardinality check
SELECT 
    table_name,
    index_name,
    cardinality
FROM information_schema.statistics
WHERE table_schema = 'crm_db'
ORDER BY cardinality DESC;

Migration Management

Migration Naming Convention

YYYY_MM_DD_HHMMSS_description.sql
2025_01_15_143000_create_customers_table.sql
2025_01_16_090000_add_index_to_customers.sql

Migration Template

-- Migration: 2025_01_15_143000_create_customers_table.sql
-- Author: Developer Name
-- Description: Create customers table with initial schema

-- Up Migration
CREATE TABLE IF NOT EXISTS customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- columns definition
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Down Migration (in separate file or commented)
-- DROP TABLE IF EXISTS customers;

Migration Tracking Table

CREATE TABLE migrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration VARCHAR(255) NOT NULL,
    batch INT NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE INDEX idx_migrations_name (migration)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Query Optimization

Query Best Practices

-- Use specific columns instead of SELECT *
SELECT id, company_name, email FROM customers WHERE status = 'active';

-- Use LIMIT for large datasets
SELECT * FROM activities ORDER BY created_at DESC LIMIT 100;

-- Use JOIN instead of subqueries when possible
SELECT c.*, COUNT(o.id) as opportunity_count
FROM customers c
LEFT JOIN opportunities o ON c.id = o.customer_id
GROUP BY c.id;

-- Use EXISTS for checking existence
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM opportunities o 
    WHERE o.customer_id = c.id AND o.stage = 'closed_won'
);

-- Batch INSERT for better performance
INSERT INTO activities (type, subject, customer_id) VALUES
    ('call', 'Follow up', 1),
    ('email', 'Proposal', 2),
    ('meeting', 'Demo', 3);

Query Analysis

-- Enable query profiling
SET profiling = 1;

-- Run query
SELECT * FROM customers WHERE status = 'active';

-- Show profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- Analyze table statistics
ANALYZE TABLE customers;

-- Check query execution plan
EXPLAIN FORMAT=JSON
SELECT c.*, COUNT(o.id) as opp_count
FROM customers c
LEFT JOIN opportunities o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.id;

Data Integrity

Constraints

-- Foreign Key Constraints
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- Check Constraints (MySQL 8.0+)
ALTER TABLE opportunities
ADD CONSTRAINT chk_probability
CHECK (probability >= 0 AND probability <= 100);

-- Default Values
ALTER TABLE customers
ALTER COLUMN status SET DEFAULT 'prospect';

-- NOT NULL Constraints
ALTER TABLE contacts
MODIFY COLUMN email VARCHAR(255) NOT NULL;

Triggers

-- Audit trigger
DELIMITER $$
CREATE TRIGGER customers_audit_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        record_id,
        action,
        user_id,
        old_values,
        new_values,
        created_at
    ) VALUES (
        'customers',
        NEW.id,
        'UPDATE',
        @current_user_id,
        JSON_OBJECT('status', OLD.status, 'assigned_to', OLD.assigned_to),
        JSON_OBJECT('status', NEW.status, 'assigned_to', NEW.assigned_to),
        NOW()
    );
END$$
DELIMITER ;

-- Business logic trigger
DELIMITER $$
CREATE TRIGGER opportunities_close_date
BEFORE UPDATE ON opportunities
FOR EACH ROW
BEGIN
    IF NEW.stage IN ('closed_won', 'closed_lost') AND OLD.stage NOT IN ('closed_won', 'closed_lost') THEN
        SET NEW.actual_close_date = CURDATE();
    END IF;
END$$
DELIMITER ;

Partitioning

Time-based Partitioning

-- Partition activities table by month
ALTER TABLE activities
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- Add new partition
ALTER TABLE activities
ADD PARTITION (
    PARTITION p202507 VALUES LESS THAN (202508)
);

-- Drop old partition
ALTER TABLE activities DROP PARTITION p202501;

Backup & Recovery

Backup Strategy

#!/bin/bash
# Daily backup script

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
DB_NAME="crm_db"

# Full backup
mysqldump \
    --host=localhost \
    --user=backup_user \
    --password=secret \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --add-drop-database \
    --databases $DB_NAME \
    --result-file=$BACKUP_DIR/full_backup_$DATE.sql

# Compress backup
gzip $BACKUP_DIR/full_backup_$DATE.sql

# Remove backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

Point-in-Time Recovery

-- Enable binary logging in my.cnf
log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M

-- Backup binary logs
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

-- Restore to specific point
mysqlbinlog \
    --start-datetime="2025-01-15 10:00:00" \
    --stop-datetime="2025-01-15 14:00:00" \
    mysql-bin.000001 | mysql -u root -p

Performance Monitoring

Key Metrics

-- Connection metrics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Query cache metrics
SHOW STATUS LIKE 'Qcache%';

-- InnoDB metrics
SHOW ENGINE INNODB STATUS;

-- Table statistics
SELECT 
    table_name,
    table_rows,
    data_length / 1024 / 1024 as data_mb,
    index_length / 1024 / 1024 as index_mb
FROM information_schema.tables
WHERE table_schema = 'crm_db'
ORDER BY data_length DESC;

-- Slow queries
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

Performance Tuning

-- Optimize tables
OPTIMIZE TABLE customers;

-- Update statistics
ANALYZE TABLE customers;

-- Check for missing indexes
SELECT 
    s.table_name,
    s.column_name,
    s.cardinality
FROM information_schema.statistics s
LEFT JOIN information_schema.key_column_usage k
    ON s.table_name = k.table_name 
    AND s.column_name = k.column_name
WHERE s.table_schema = 'crm_db'
    AND k.column_name IS NULL
    AND s.cardinality > 1000;

Security

User Management

-- Create application user
CREATE USER 'crm_app'@'%' IDENTIFIED BY 'strong_password';

-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON crm_db.* TO 'crm_app'@'%';

-- Create read-only user for reporting
CREATE USER 'crm_readonly'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON crm_db.* TO 'crm_readonly'@'%';

-- Create backup user
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

-- Revoke permissions
REVOKE DELETE ON crm_db.* FROM 'crm_app'@'%';

-- Show user privileges
SHOW GRANTS FOR 'crm_app'@'%';

Data Encryption

-- Enable encryption at rest
ALTER TABLE customers ENCRYPTION='Y';

-- Check encryption status
SELECT 
    table_schema,
    table_name,
    create_options
FROM information_schema.tables
WHERE table_schema = 'crm_db'
    AND create_options LIKE '%ENCRYPTION%';

Maintenance

Regular Tasks

-- Weekly maintenance script
-- 1. Check table integrity
CHECK TABLE customers, contacts, opportunities;

-- 2. Optimize fragmented tables
SELECT 
    table_name,
    data_free / 1024 / 1024 as fragmentation_mb
FROM information_schema.tables
WHERE table_schema = 'crm_db'
    AND data_free > 100 * 1024 * 1024;

-- 3. Update statistics
ANALYZE TABLE customers, contacts, opportunities;

-- 4. Purge old data
DELETE FROM audit_log WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM activity_log WHERE created_at < DATE_SUB(NOW(), INTERVAL 180 DAY);

-- 5. Check for unused indexes
SELECT 
    t.table_name,
    s.index_name,
    s.cardinality
FROM information_schema.statistics s
JOIN information_schema.tables t ON s.table_name = t.table_name
WHERE t.table_schema = 'crm_db'
    AND s.cardinality = 0;

---

Last Updated: January 2025 Version: 1.0.0