Database Design Checklist: MySQL Audit Queries to Run

Before a schema goes to production, and again every few months, review it with a checklist. Many of the checks can be automated with queries against information_schema, which this lesson shows with real results.

The checklist

Structure

[ ] Every table has a primary key (preferably a short surrogate key).
[ ] Every table is InnoDB (unless you chose otherwise on purpose).
[ ] Each table is about one thing, and the data is in 3NF unless a documented denormalization says otherwise (Normalization).
[ ] Many-to-many relationships use junction tables with a composite primary key.

Integrity

[ ] Foreign keys exist for every relationship, with deliberate ON DELETE actions (Foreign Key Actions).
[ ] Required columns are NOT NULL. Optional ones are NULL for a reason.
[ ] Natural identifiers have UNIQUE constraints.
[ ] CHECK constraints protect simple rules (non-negative amounts, date order).
[ ] Statuses are ENUM or lookup tables, not free text.

Types

[ ] Money is DECIMAL (or integer cents), never FLOAT.
[ ] Dates are DATE, DATETIME or TIMESTAMP, not text.
[ ] Foreign key columns have exactly the same type as the key they reference.
[ ] Text uses utf8mb4, and string lengths are realistic (Character Sets).

Performance

[ ] Each foreign key column and each frequent WHERE or JOIN column is indexed.
[ ] No duplicate or unused indexes (Index Design Rules).
[ ] The main queries were checked with EXPLAIN (EXPLAIN).

Operations

[ ] Names follow a written convention (Naming Conventions).
[ ] Changes go through versioned migrations (Schema Migrations).
[ ] Application accounts have least privilege (Application Users).
[ ] Backups exist and a restore was tested (Backup and Restore).

Automated audits

First a deliberately imperfect schema, so you can see the queries find problems:

query.sql

SQL

CREATE TABLE customers (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL, UNIQUE KEY uq_customers_email (email));
CREATE TABLE orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, total DECIMAL(10,2) NOT NULL, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id));
CREATE TABLE audit_log (message VARCHAR(200), logged_at DATETIME);
CREATE TABLE legacy_notes (id INT PRIMARY KEY, note TEXT) ENGINE = MyISAM;
CREATE TABLE tags (id INT PRIMARY KEY, label VARCHAR(30)) DEFAULT CHARSET = latin1;

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

1. Tables without a primary key

query.sql

SQL

SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = DATABASE() AND t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL
ORDER BY t.table_name;

Output

OUTPUT

+------------+
| TABLE_NAME |
+------------+
| audit_log  |
+------------+
1 row in set

audit_log has no primary key. Replication, row identity and many tools depend on one.

2. Tables that are not InnoDB

query.sql

SQL

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' AND engine <> 'InnoDB' ORDER BY table_name;

Output

OUTPUT

+--------------+--------+
| TABLE_NAME   | ENGINE |
+--------------+--------+
| legacy_notes | MyISAM |
+--------------+--------+
1 row in set

3. Tables or columns that are not utf8mb4

query.sql

SQL

1
2

SELECT table_name, column_name, character_set_name FROM information_schema.columns
WHERE table_schema = DATABASE() AND character_set_name IS NOT NULL AND character_set_name <> 'utf8mb4' ORDER BY table_name, column_name;

Output

OUTPUT

+------------+-------------+--------------------+
| TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME |
+------------+-------------+--------------------+
| tags       | label       | latin1             |
+------------+-------------+--------------------+
1 row in set

4. Foreign keys whose columns are not the start of an index

MySQL creates an index for a foreign key automatically if there is none, so in InnoDB this audit mostly finds mistaken designs. A different useful audit is the one that finds indexes with no use, from sys (Performance Schema and sys). Here, list each foreign key and the index that supports it:

query.sql

SQL

SELECT k.table_name, k.constraint_name, k.column_name,
       (SELECT GROUP_CONCAT(DISTINCT s.index_name ORDER BY s.index_name) FROM information_schema.statistics s
        WHERE s.table_schema = k.table_schema AND s.table_name = k.table_name AND s.column_name = k.column_name AND s.seq_in_index = 1) AS supporting_index
FROM information_schema.key_column_usage k
WHERE k.table_schema = DATABASE() AND k.referenced_table_name IS NOT NULL
ORDER BY k.table_name, k.constraint_name;

Output

OUTPUT

+------------+--------------------+-------------+--------------------+
| TABLE_NAME | CONSTRAINT_NAME    | COLUMN_NAME | supporting_index   |
+------------+--------------------+-------------+--------------------+
| orders     | fk_orders_customer | customer_id | fk_orders_customer |
+------------+--------------------+-------------+--------------------+
1 row in set

5. Nullable columns (to review, not necessarily wrong)

query.sql

SQL

1
2

SELECT table_name, column_name, column_type FROM information_schema.columns
WHERE table_schema = DATABASE() AND is_nullable = 'YES' ORDER BY table_name, ordinal_position;

Output

OUTPUT

+--------------+-------------+--------------+
| TABLE_NAME   | COLUMN_NAME | COLUMN_TYPE  |
+--------------+-------------+--------------+
| audit_log    | message     | varchar(200) |
| audit_log    | logged_at   | datetime     |
| legacy_notes | note        | text         |
| tags         | label       | varchar(30)  |
+--------------+-------------+--------------+
4 rows in set

For each nullable column, ask: can this value really be unknown or not applicable? If every row must have a value, add NOT NULL.

6. Same column name, different types

The audit from Naming Conventions catches mismatches such as customer_id INT next to customer_id BIGINT. Run it after every migration.

7. Table sizes (find the big ones)

query.sql

SQL

SELECT table_name, engine, ROUND((data_length + index_length) / 1024) AS size_kb FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' ORDER BY size_kb DESC, table_name;

Output

OUTPUT

+--------------+----------+--------+
| TABLE_NAME   | has_rows | ENGINE |
+--------------+----------+--------+
| audit_log    |        0 | InnoDB |
| customers    |        0 | InnoDB |
| legacy_notes |        0 | MyISAM |
| orders       |        0 | InnoDB |
| tags         |        0 | InnoDB |
+--------------+----------+--------+
5 rows in set

(data_length + index_length is the size on disk, and table_rows is only an estimate for InnoDB, so use COUNT(*) for exact row counts. Even empty tables use some pages. More in Table Maintenance.)

Turn the audit into a habit

Put the queries in a script in the repository, and run it in CI against the schema produced by your migrations. Fail the build if a table has no primary key.
Run them against production as a read-only account every month.
Add each new rule that a real incident taught you.

A short design review conversation

When reviewing a new table, ask:

What single fact does one row represent?
What uniquely identifies it, and is that enforced?
What happens when its parent is deleted?
What are the three most common queries, and which index serves each?
How big will it be in a year, and what is the plan for old data?
Does it hold personal data (Auditing and Privacy)?

lightbulb

A short checklist beats a long document

A checklist that is run is better than a design guide that is not. Keep yours short, automate what you can, and update it after every incident.