MySQL for Application Developers

A database is not just a place to put application data. Schema constraints, indexes, transactions, backups, and query design determine whether the application remains correct and responsive as it grows.

Design for Correctness First

Application validation improves the user experience, but the database is the final authority for uniqueness, relationships, and transactional consistency. Good schemas make invalid states difficult to store.

Schema and Integrity

Queries and Performance

Operations

Practical Principles

Use utf8mb4 consistently.
Use UTC for stored timestamps unless local wall time is the domain value.
Enforce uniqueness and relationships in the database.
Index for real query patterns.
Test restores, not only backups.

A Small Starting Schema

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(254) NULL,
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
        ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email),
    KEY idx_customers_status_name (status, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;