Cheat SheetsMySQLSchema Design

Schema Design — Cheat Sheet

MySQL · 4 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Schema Design

MySQL4 topicsQuick revision reference

Normalisation (1NF–BCNF)

1NF eliminates repeating groups; 2NF removes partial dependencies; 3NF removes transitive dependencies; BCNF handles overlapping candidate keys — normalise for write integrity.

✓1NF: atomic column values, no repeating groups, unique rows
✓2NF: every non-key column depends on the entire composite primary key (no partial dependency)
✓3NF: no non-key column depends on another non-key column (no transitive dependency)
✓BCNF: every determinant must be a candidate key (handles overlapping candidate keys)
✓Most OLTP schemas target 3NF; BCNF splits further but may reintroduce other anomalies
✓Denormalisation is a deliberate trade-off — materialised columns/tables for read-heavy paths

MySQL — 1NF and 2NF normalisation

-- 1NF VIOLATION: repeating groups in one column
CREATE TABLE articles_bad (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags VARCHAR(500)   -- "java,spring,jpa"  ← NOT 1NF
);

-- 1NF FIX: extract to an article_tags table
CREATE TABLE article_tags (
    article_id INT,
    tag        VARCHAR(50),
    PRIMARY KEY (article_id, tag),
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 2NF VIOLATION: composite PK, partial dependency
CREATE TABLE order_items_bad (
    order_id     INT,
    product_id   INT,
    quantity     INT,
    product_name VARCHAR(100),  -- depends only on product_id, NOT the full PK
    PRIMARY KEY (order_id, product_id)
);

-- 2NF FIX: move product_name to the products table
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Denormalisation

Strategically add redundant data (precomputed totals, copied columns) to reduce JOIN overhead on read-heavy workloads; balance query speed against write complexity and consistency.

✓Denormalise only after profiling — premature denormalisation adds complexity for no gain
✓Every denormalised copy is a consistency liability; a trigger or event must keep all copies in sync
✓Generated columns (STORED or VIRTUAL) automate simple expressions without application code
✓Snapshot pattern (copy at insert time) is intentional — preserves historical state, not a bug
✓Summary tables trade staleness for speed; suitable for reporting, not operational queries
✓Consider MySQL views first — they add no storage; denormalise when views are too slow

SQL — trigger-maintained precomputed aggregate

-- Precomputed total on orders table (application updates on each line-item change)
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00;

-- Trigger to keep total_amount in sync
DELIMITER $$
CREATE TRIGGER trg_update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(qty * unit_price), 0)
        FROM order_items WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END$$
DELIMITER ;

Table Partitioning

RANGE, LIST, HASH, and KEY partitioning split large tables into smaller physical segments; partition pruning lets the optimiser scan only relevant partitions for filtered queries.

✓RANGE is best for time-series data; old partitions can be DROPped instantly.
✓LIST partitions by discrete values (enums, region codes).
✓HASH/KEY spread rows evenly — pruning only applies when the hash key is equality-filtered.
✓The partition key must be part of every UNIQUE and PRIMARY key.
✓Foreign keys are not supported on partitioned tables.
✓Partition pruning is only guaranteed when the WHERE clause filters on the partition expression.

SQL — RANGE partitioning on datetime

-- Partition orders by year of created_at
CREATE TABLE orders (
    id         BIGINT       NOT NULL AUTO_INCREMENT,
    customer_id BIGINT      NOT NULL,
    total      DECIMAL(10,2),
    created_at DATETIME     NOT NULL,
    PRIMARY KEY (id, created_at)   -- partition key must be in PK
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Add a new partition for 2025 (reorganise p_future)
ALTER TABLE orders
    REORGANIZE PARTITION p_future INTO (
        PARTITION p2025 VALUES LESS THAN (2026),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );

-- Drop 2022 data instantly — no row-level DELETE
ALTER TABLE orders DROP PARTITION p2022;

-- Verify pruning
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- partitions column shows only p2024, p_future

JSON Data Type & Functions

MySQL 5.7+ stores JSON as a binary type with path expressions (->>, JSON_EXTRACT, JSON_SET); generated virtual columns on JSON paths can be indexed for efficient JSON queries.

✓MySQL JSON columns store binary-encoded JSON — validated on insert, supports partial path-based updates without full rewrite
✓-> returns a quoted JSON value; ->> returns the unquoted scalar string — use ->> for string comparisons and WHERE clauses
✓JSON columns cannot be directly indexed — create a virtual generated column on a path and index the virtual column
✓Multi-value indexes (MySQL 8.0.17+) enable efficient MEMBER OF() queries on JSON arrays
✓JSON_SET/JSON_REMOVE modify individual paths; JSON_MERGE_PATCH merges/overwrites a document with a patch object
✓JSON_TABLE() (MySQL 8.0) pivots JSON arrays to relational rows in a FROM clause — useful for reports and unnesting

SQL — JSON insert, path extraction, and partial updates

-- Create table with JSON column
CREATE TABLE products (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(200),
    attributes JSON NOT NULL   -- validated on insert
);

-- Insert JSON
INSERT INTO products (name, attributes)
VALUES ('Widget Pro', '{"color":"blue","weight":0.5,"tags":["sale","new"]}');

-- Read with -> (returns quoted string) and ->> (unquoted)
SELECT
    attributes->'$.color'        AS color_quoted,    -- "blue"
    attributes->>'$.color'       AS color_plain,     -- blue
    attributes->'$.tags[0]'      AS first_tag,       -- "sale"
    JSON_EXTRACT(attributes, '$.weight') AS weight;  -- 0.5

-- Update single path (does not rewrite whole document)
UPDATE products
SET attributes = JSON_SET(attributes,
    '$.color', 'red',
    '$.price', 9.99)
WHERE id = 1;

-- Remove a key
UPDATE products SET attributes = JSON_REMOVE(attributes, '$.tags') WHERE id = 1;

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql

Cheat Sheet

Schema Design

MySQL

4 topics · swipe →

AiCanCode.org

Schema Design

Normalisation (1NF–BCNF)

✓1NF: atomic column values, no repeating groups, unique rows
✓2NF: every non-key column depends on the entire composite primary key (no partial dependency)
✓3NF: no non-key column depends on another non-key column (no transitive dependency)
✓BCNF: every determinant must be a candidate key (handles overlapping candidate keys)
✓Most OLTP schemas target 3NF; BCNF splits further but may reintroduce other anomalies

-- 1NF VIOLATION: repeating groups in one column
CREATE TABLE articles_bad (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags VARCHAR(500)   -- "java,spring,jpa"  ← NOT 1NF
);

-- 1NF FIX: extract to an article_tags table
CREATE TABLE article_tags (
…

AiCanCode.org

Schema Design

Denormalisation

✓Denormalise only after profiling — premature denormalisation adds complexity for no gain
✓Every denormalised copy is a consistency liability; a trigger or event must keep all copies in sync
✓Generated columns (STORED or VIRTUAL) automate simple expressions without application code
✓Snapshot pattern (copy at insert time) is intentional — preserves historical state, not a bug
✓Summary tables trade staleness for speed; suitable for reporting, not operational queries

-- Precomputed total on orders table (application updates on each line-item change)
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00;

-- Trigger to keep total_amount in sync
DELIMITER $$
CREATE TRIGGER trg_update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
…

AiCanCode.org

Schema Design

Table Partitioning

✓RANGE is best for time-series data; old partitions can be DROPped instantly.
✓LIST partitions by discrete values (enums, region codes).
✓HASH/KEY spread rows evenly — pruning only applies when the hash key is equality-filtered.
✓The partition key must be part of every UNIQUE and PRIMARY key.
✓Foreign keys are not supported on partitioned tables.

-- Partition orders by year of created_at
CREATE TABLE orders (
    id         BIGINT       NOT NULL AUTO_INCREMENT,
    customer_id BIGINT      NOT NULL,
    total      DECIMAL(10,2),
    created_at DATETIME     NOT NULL,
    PRIMARY KEY (id, created_at)   -- partition key must be in PK
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
…

AiCanCode.org

Schema Design

JSON Data Type & Functions

✓MySQL JSON columns store binary-encoded JSON — validated on insert, supports partial path-based updates without full rewrite
✓-> returns a quoted JSON value; ->> returns the unquoted scalar string — use ->> for string comparisons and WHERE clauses
✓JSON columns cannot be directly indexed — create a virtual generated column on a path and index the virtual column
✓Multi-value indexes (MySQL 8.0.17+) enable efficient MEMBER OF() queries on JSON arrays
✓JSON_SET/JSON_REMOVE modify individual paths; JSON_MERGE_PATCH merges/overwrites a document with a patch object

-- Create table with JSON column
CREATE TABLE products (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(200),
    attributes JSON NOT NULL   -- validated on insert
);

-- Insert JSON
INSERT INTO products (name, attributes)
…

AiCanCode.org

Master Schema Design

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/mysql