MySQL Database Design Tutorial: From Schema to Production

MySQL powers more web applications than any other relational database. It is the "M" in the LAMP stack, the default in countless shared hosting environments, and still the database of choice for many high-traffic production systems. But MySQL has important quirks and design decisions that differ from PostgreSQL or SQLite — and knowing them is essential for building a schema that performs well and avoids subtle bugs.

This tutorial walks through MySQL-specific schema design from initial design to production deployment, covering every decision point that matters.

Storage Engines: Always Use InnoDB

MySQL supports multiple storage engines, but the only one you should use for application tables in 2026 is InnoDB. InnoDB supports foreign keys, transactions, row-level locking, and crash recovery. The legacy MyISAM engine lacks foreign key support and uses table-level locking, making it unsuitable for any application with concurrent writes.

InnoDB is the default since MySQL 5.5, so you should not need to specify it explicitly. However, if you are working with an older database or migrating from another system, verify with SHOW TABLE STATUS that all tables use InnoDB. If any tables use MyISAM, convert them: ALTER TABLE table_name ENGINE = InnoDB;

Integer Types: INT vs BIGINT

MySQL's integer types differ from PostgreSQL's in naming and range. Use `INT` (4 bytes, signed range -2.1B to 2.1B, unsigned 0 to 4.3B) for most IDs and counts. Use `BIGINT` (8 bytes, signed range ±9.2 quintillion) for tables that will grow beyond 2 billion rows. The `UNSIGNED` modifier doubles the positive range at no storage cost — INT UNSIGNED goes up to 4,294,967,295 — and is a good choice for ID columns that will never be negative.

For auto-incrementing primary keys, the declaration is: id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY. Prefer BIGINT UNSIGNED AUTO_INCREMENT for any table that might scale significantly. The TINYINT(1) type is MySQL's conventional boolean (0 or 1); ORMs like Laravel and Doctrine map PHP booleans to this type automatically.

VARCHAR vs TEXT: A Critical Distinction

Unlike PostgreSQL, MySQL stores `VARCHAR` and `TEXT` differently, and the distinction matters for performance. VARCHAR(n) is stored inline with the row for values up to 65,535 bytes (within the row format's limit). TEXT (and MEDIUMTEXT, LONGTEXT) stores the data off-page and only keeps a pointer inline — this makes it slower to read for small values.

The practical guidance: use VARCHAR(n) for strings where you know a reasonable upper bound — names, emails, slugs, status codes. Use TEXT for truly open-ended content — blog post bodies, markdown documents, log messages. One important caveat: MySQL cannot use a TEXT column as part of a UNIQUE index or a regular index without a prefix length (INDEX (column(191))). If you need to index the column, use VARCHAR(191) (the maximum for utf8mb4 with the default index length).

Always Use utf8mb4, Not utf8

This is one of MySQL's most notorious gotchas. MySQL's utf8 character set is not real UTF-8 — it only supports characters that fit in 3 bytes, which excludes emoji and many non-Latin scripts. Always use `utf8mb4`, which is the genuine 4-byte UTF-8 implementation.

Set the character set at the server, database, table, and column levels. The safest approach is to configure MySQL's defaults: ``sql CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; And specify it on VARCHAR and TEXT columns: sql name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL For case-insensitive comparisons (the common case), use utf8mb4_unicode_ci. For case-sensitive comparisons, use utf8mb4_bin`.

DATETIME vs TIMESTAMP

MySQL has two types for storing date and time values. `TIMESTAMP` stores values as a UTC Unix timestamp internally and converts to/from the server's local timezone on read/write. It supports a range of 1970 to 2038 — the infamous "Year 2038 problem." `DATETIME` stores values as-is with no timezone conversion, supporting a range of 1000 to 9999.

For created_at and updated_at columns, use DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP and DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. This avoids the 2038 limit and the confusion of implicit timezone conversion. For columns that store user-facing dates and times, store them in UTC as DATETIME and convert to the user's timezone in application code.

Foreign Keys and InnoDB: Syntax Matters

MySQL's foreign key syntax is slightly different from PostgreSQL's, and it is strict about certain rules. Foreign key columns must have the same type and sign as the referenced primary key: if users.id is INT UNSIGNED, then orders.user_id must also be INT UNSIGNED. The foreign key column must be indexed (MySQL does not always create this index automatically in all versions). ``sql CREATE TABLE orders ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_orders_user_id (user_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE );

Common MySQL Gotchas to Avoid

Several MySQL behaviors catch developers off guard. Silent truncation: older MySQL versions truncate strings that exceed a VARCHAR length without raising an error in non-strict mode. Enable strict mode: SET sql_mode = 'STRICT_TRANS_TABLES,...' or configure it in my.cnf. Zero dates: MySQL allows 0000-00-00 as a date value by default; disable this with NO_ZERO_DATE in sql_mode. GROUP BY behavior: MySQL historically allowed selecting non-aggregated columns not in the GROUP BY; ONLY_FULL_GROUP_BY mode enforces standard SQL behavior and should always be enabled.

Migration Strategies for MySQL

For evolving MySQL schemas in production, two approaches dominate. Online DDL (supported since MySQL 5.6+): many ALTER TABLE operations in InnoDB can be performed with ALGORITHM=INPLACE, LOCK=NONE, allowing the table to remain readable and writable during the migration. Check the MySQL documentation for which operations support online DDL. For large tables where online DDL is not supported, use pt-online-schema-change (Percona Toolkit) or gh-ost (GitHub's online schema tool) to perform the migration without locking the table.

Designing MySQL Schemas with ER Flow

ER Flow supports MySQL data types natively, including INT, BIGINT UNSIGNED, VARCHAR, TEXT, DATETIME, TIMESTAMP, and TINYINT(1) for booleans. You can design your full schema visually — tables, columns, foreign keys, indexes — and generate MySQL-compatible migration SQL directly from the ER diagram. The generated migrations use proper MySQL syntax including ENGINE=InnoDB, correct character set declarations, and explicit index creation for foreign key columns. Start with the visual model, generate the migration, and deploy with confidence.