Schema Design for MySQL

General database design (requirements → ER model → normalized tables, covered in depth in the SQL course) applies to MySQL, plus InnoDB-specific considerations: clustered primary keys, secondary-index size, row format, data types, charset, and operational f…

Concept

Why It Matters

Schema decisions are hard to change once data grows. MySQL-aware design avoids slow inserts (random PKs), bloated indexes, replication problems (tables without PKs), and painful migrations.

Syntax

Design rules are expressed through CREATE TABLE definitions, constraints, and indexes.

Example

34.1 Normalization recap (to 3NF) MySQL example violation Atomic values, no repeating groups ecus_list = 'BMS,VCU,BCM' column

No partial dependency on part of a composite key order_items(order_id, product_id, product_name) No transitive dependency orders(customer_id, customer_city)

Fix by splitting into related tables with foreign keys (as in the course sample database).

Denormalize deliberately for read performance: summary tables maintained by events/triggers, JSON snapshots, historical values (price at time of order).

34.2 InnoDB-aware design rules Every table has a primary key — short, stable, increasing (INT/BIGINT UNSIGNED AUTO_INCREMENT or time-ordered UUID in BINARY(16)).

Natural keys as UNIQUE constraints (VIN, serial number, email) alongside surrogate PKs.

Identical types and collations for FK columns and referenced columns.

Index every foreign key and design composite indexes for top queries.

Smallest adequate types: TINYINT for small codes, DATE not DATETIME for dates, CHAR(n) for fixed codes.

utf8mb4 everywhere.

Separate hot and cold data: large TEXT/BLOB/JSON columns in a 1:1 side table if rarely read.

Time-series tables: partition by time; keep raw data retention policies; keep summaries in separate tables.

Soft deletes only when required; add deleted_at and include it in unique keys if needed (MySQL has no partial indexes — use a generated column trick: active_key = IF(deleted_at IS NULL, email, NULL) with a UNIQUE index, since multiple NULLs are allowed).

Auditing: created_at, updated_at (ON UPDATE CURRENT_TIMESTAMP), and history tables via triggers where needed.

Soft-delete uniqueness example:

34.3 Relationship patterns 34.4 Naming conventions lowercase, underscore plural snake_case (or singular — be consistent) snake_case with units

duration_s, odometer_km, voltage_mv ix_<table>_<cols> / uq_<table>_<cols>

Avoid reserved words (order, key, group, desc, range, signal, offset, rank) as names.

34.5 Schema migrations Keep DDL in versioned migration files (Flyway, Liquibase, Alembic, Laravel/Rails migrations, Prisma, Atlas, Skeema).

Use ALGORITHM=INSTANT/INPLACE, LOCK=NONE and test duration on production-size copies.

For big tables, use online schema change tools (gh-ost, pt-online-schema-change).

Expand/contract pattern: add new column → backfill → switch reads/writes → drop old column later.

CREATE TABLE app_users (
    user_id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(190) NOT NULL,
    deleted_at  DATETIME NULL,
    email_active VARCHAR(190) GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
    UNIQUE KEY uq_app_users_email_active (email_active)
);
-- Only one ACTIVE row per email; deleted rows can repeat the email.

-- 1:N — FK on the many side
CREATE TABLE ecu_list (ecu_id INT UNSIGNED PRIMARY KEY, vehicle_id INT UNSIGNED NOT NULL,
                       FOREIGN KEY (vehicle_id) REFERENCES vehicles (vehicle_id));

-- 1:1 — FK is also PK (or FK + UNIQUE)
CREATE TABLE vehicle_ev_details (vehicle_id INT UNSIGNED PRIMARY KEY, battery_kwh DECIMAL(5,1) NOT NULL,
                                 FOREIGN KEY (vehicle_id) REFERENCES vehicles (vehicle_id));

-- M:N — junction table with composite PK and reverse index
CREATE TABLE test_case_requirements (
    test_case_id    INT UNSIGNED NOT NULL,
    requirement_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (test_case_id, requirement_id),
    KEY ix_tcr_requirement (requirement_id)
);

Expected Result

A MySQL schema where every table has a compact PK, all relationships are enforced with indexed FKs, types are minimal and consistent, and large time-series tables are partitioned.

Common Mistakes

Tables without PKs (bad for InnoDB and replication).

CHAR(36) UUID PKs; mismatched FK types.

Comma-separated lists in columns.

Reserved words as column names (signal, offset, range are common in engineering schemas).

Huge JSON/TEXT columns in hot tables.

Best Practices

Normalize to 3NF, then denormalize with evidence.

Design for InnoDB's clustered index.

Document columns with COMMENT, including units.

Migrate schemas through code review and CI.

Interview Relevance

High in design interviews: "Design a schema for ...", "How do you choose a PK in MySQL?", "How do you model M:N?", "How do you implement soft-delete uniqueness in MySQL?".

Practice Questions

Design tables for vehicles, ECUs, software versions, and install history; list PKs, FKs, and indexes.

Implement "one active subscription per user" in MySQL without partial indexes.

Plan a zero-downtime migration that renames a heavily used column.