Essential MySQL Database Design and Coding Standards for Reliable Systems

Database Command Conventions

All database object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if necessary, wrap them in single quotes.

Names should be descriptive and no longer than 32 characters.

Temporary tables use the tmp_ prefix with a date suffix; backup tables use bak_ with a date or timestamp suffix.

Columns storing identical data must have the same name and type to prevent implicit type conversion and index loss.

Basic Database Design Standards

All tables must use the InnoDB storage engine to benefit from transactions, row‑level locking, and better concurrency.

Use UTF‑8 (or UTF8MB4 for emoji) as the default character set to avoid encoding issues and index inefficiencies.

Every table and column should have a comment for documentation and data‑dictionary maintenance.

Keep single‑table row counts under 5 million; use archiving, sharding, or partitioning for larger datasets.

Use partition tables cautiously; choose partition keys wisely and prefer physical sharding for very large data.

Separate hot and cold data to reduce table width and improve cache hit rates.

Do not create placeholder columns; they are hard to name meaningfully and can cause locking when altered.

Avoid storing large binary objects (images, files) in the database; store them on a file server and keep only the path.

Never perform stress testing on a production database.

Never connect production databases directly from development or test environments.

Database Field Design Standards

Choose the smallest suitable data type; unsigned integers store twice the range of signed.

Avoid TEXT and BLOB columns; if needed, isolate them in separate tables and avoid SELECT *.

Large TEXT/BLOB fields prevent index usage and force temporary tables, degrading performance.

Avoid ENUM types; they require costly ALTER operations and hinder ordering.

Define columns as NOT NULL to prevent extra index space and simplify comparisons.

Store timestamps with TIMESTAMP (4 bytes) or DATETIME (8 bytes); use DATETIME when the range exceeds 1970‑2038.

Financial amounts must use DECIMAL for exact precision; avoid FLOAT/DOUBLE for monetary values.

Remember that VARCHAR(N) counts characters, not bytes; UTF‑8 can consume up to 3 bytes per character.

Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase optimizer planning time and can degrade performance.

Do not create an index on every column; prioritize columns used in WHERE, ORDER BY, GROUP BY, and join conditions.

Every InnoDB table must have a primary key; avoid frequently updated columns, UUIDs, or composite keys as primary keys; prefer auto‑increment IDs.

Common index column recommendations:

Columns appearing in WHERE clauses.

Columns used in ORDER BY, GROUP BY, or DISTINCT.

Join keys for multi‑table queries.

Prefer covering indexes that contain all required columns to avoid secondary‑index lookups and convert random I/O to sequential I/O.

Avoid redundant and duplicate indexes (e.g., INDEX(a,b,c) plus INDEX(a,b) plus INDEX(a)).

Use UNION ALL instead of UNION when duplicate rows are impossible.

Index SET Conventions

Minimize foreign key constraints; if used, ensure indexed reference columns and enforce referential integrity at the application layer.

SQL Development Standards

Prefer prepared statements to reuse execution plans and prevent SQL injection.

Avoid implicit type conversion; it can invalidate indexes (e.g., comparing an integer column to a quoted string).

Leverage existing indexes; avoid leading wildcards in LIKE patterns.

When a query can use only one column of a composite index for range scans, place the range column on the right side of the index.

Replace unnecessary subqueries with JOIN where possible.

Limit the number of tables in a single query (MySQL allows up to 61, but keeping it ≤5 prevents memory overflow and large temporary tables).

Do not use SELECT *; list required columns to enable covering indexes and reduce I/O.

Always specify column lists in INSERT statements.

Prefer IN over multiple OR conditions; keep the list size ≤500.

Avoid ORDER BY RAND(); generate random values in application code instead.

Do not apply functions or calculations to columns in WHERE clauses, as this prevents index usage.

WHERE create_time >= '20190101' AND create_time < '20190102'

Split complex large SQL statements into smaller ones to enable parallel execution.

Database Operational Behavior Standards

For batch writes exceeding one million rows, split into smaller transactions to avoid master‑slave lag and massive binary logs.

Use pt-online-schema-change for schema changes on large tables to avoid locking and long replication delays.

Never grant SUPER privileges to application accounts; reserve it for DBA use only.

Apply the principle of least privilege to application database accounts; avoid DROP permissions and cross‑database access.

By following these conventions, developers can build MySQL databases that are performant, maintainable, and secure.