I sat down and wrote up binding conventions for MySQL schema design, forcing myself to answer the question why for every single rule. That answer is half the value: a rule that can't defend itself is dogma, and the first unhappy colleague will rightly sweep it off the table.

One idea runs through everything below: the database is integrity's last line of defence. The application validates for the user's sake — nice messages, in their language, along their code path. The database guarantees. Across imports, migrations, hand-written SQL in a console, a second application, buggy code, and that colleague who runs an UPDATE without a WHERE on production.

Agreements don't survive in data. Only what the schema enforces does.

Everything below applies to MySQL 8.0.16 and newer (because of enforced CHECK constraints) and to InnoDB.

Rule zero: strict mode

Without a strict sql_mode the rest of this article is pointless, because every rule below it degrades from a guarantee into an effort with a silent fallback:

without strict mode (silently) with strict mode (loudly)
ENUM outside the list → stores '' error Data truncated
overflowing INT UNSIGNED → clamps to max error out of range
longer input into VARCHAR(20) → truncates error
'0000-00-00' → stored happily error
missing NOT NULL column → 0 or '' error

Set it on the server, not on a connection anyone can override:

STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

And do it from day one. Turning strict on over a database that already holds truncated and zero data means failures at the first UPDATE of an affected row. That is, six months later, on a Friday evening.

1. The table is called post, not posts

Yes, this one can start an afternoon-long argument, and the plural camp has both habit and a few frameworks on its side. So let's get it over with. The plural is a shade more SQL-friendly, SELECT * FROM posts reads like a sentence. Against that stand two reasons that outlive your preferences:

  • The singular maps 1:1. With the plural, the name has to be translated all the way down: class Post, table posts, and one more converter in every layer.
  • And it keeps a family of tables together. In a schema listing, post stands at the head of its relatives post_slug and post_tag, whereas posts breaks away alphabetically and ends up after them. With three tables that's a detail. With three hundred it's the difference between orientation and searching.

The rest of the naming rules aren't controversial at all. They all rest on the fact that a name in a database is a public interface, not an internal detail. Your code leans on it, but so do saved queries, reports, exports, integrations, and that Excel sheet the sales department built on top of your database. Renaming a column is therefore an intervention of the same calibre as changing the signature of a public method in a library: you break consumers you don't even know about. So you name the thing that lasts.

  • snake_case, lowercase. Nothing that would need quoting. blogId has to be quoted in PostgreSQL, because unquoted identifiers get folded to lowercase — which means blogId and blogid can happily start living side by side as two different things.
  • PK always id, FK <singular>_id, self-reference parent_id. When several foreign keys point at one table, the name carries the role, not the target: author_id, reporter_id. Where it points is what the FK constraint is for.
  • Module prefixes only once it starts to hurt (shop_order, shop_product). Inside the module the FK then needs no prefix, the table already carries the namespace.

And what lasts is meaning, not implementation. The line between them is finer than it looks: the unit is meaning, the type is implementation. weight_grams and timeout_seconds yes, because a bare number would be ambiguous. amount_int and data_json no, they merely retell what the schema already says.

2. NOT NULL, and a DEFAULT that doesn't lie

Nullable only when the absence of a value is a fact about the world, not a technical state. With numbers and dates it decides itself, there is no empty value there and NULL is the only truth about absence.

It gets interesting with strings, because there '' exists and suddenly you have two states. Pick one and write it into the schema. In most cases the answer is NOT NULL DEFAULT ''; the application then tests !== '' and is done:

`meta_title` varchar(160) NOT NULL DEFAULT '',

That ambiguity is in fact worse than it looks: in PHP both null and '' are falsy, so the distinction you're painstakingly maintaining in the database is one the application mostly can't even see. So that second state is not only redundant, it's invisible as well.

An exception does exist, it's just rarer than people think: when NULL and '' genuinely mean different things, say '' as “deliberately blank, don't inherit from the parent” versus NULL as “not filled in”. Then nullable — but that meaning goes into a COMMENT, or nobody will decode it a year from now.

And why is it worth being frugal with NULL in the first place? Because it doesn't behave the way you expect:

  • WHERE col <> 'x' won't return NULL rows. The most common silent bug there is.
  • col NOT IN (subquery) returns nothing at all if the subquery contains a single NULL.
  • UNIQUE treats NULL values as mutually distinct, so any number of them pass. GROUP BY, by contrast, lumps them into one group.

So much for NULL. The other half of this rule is DEFAULT, and it's the same story: a semantic claim, not a patch.

A default only when the value is domain-meaningful at the moment the row is born. A mandatory field with no value should be allowed to fail.

The most common mistake is filling in a default just so the INSERT won't fail. That inserts a lie indistinguishable from a real value and cancels exactly the protection strict mode gave you. status DEFAULT 'draft' yes, an order really does start as a draft. price DEFAULT 0 on a mandatory price no — that's a zero nobody can tell apart from a gift or an import bug.

3. _at is a moment, _date is a day

Two suffixes, two types, and the difference isn't cosmetic.

  • A moment in the row's life: suffix _at, type datetime. created_at, published_at, deleted_at. In the future too, expires_at is still a moment. The suffix follows the type, not the past tense.
  • A calendar day independent of any zone: {noun}_date, type date. birth_date, invoice_date, due_date.

A calendar day stored as datetime is a bug waiting to happen, and it's worth thinking through why. Midnight gets glued to the date. The moment the application starts converting time zones, which sooner or later it will, midnight shifts an hour backwards: 15 March 00:00 turns into 14 March 23:00. A date of birth is suddenly a day earlier and nobody works out why for a long time, because that hour isn't visible in the date. A calendar day has no zone, so don't give it one.

The boundary isn't always sharp and that's fine: valid_from and valid_to carry no suffix and that's alright. The choice of type still applies, it's just decided by the domain. A subscription runs from a moment, so datetime. A coupon is valid from a day, so date.

Meta columns should share one vocabulary across the schema: created_at everywhere, never mixing in create_time and date_added. And the mirror image: the same name must not mean two different things in two tables. When updated_at means “whenever anyone touched the row” in one table and “when sales last edited the price list” in another, a query that joins the two and sorts by date returns nonsense. And nobody notices, because the name promised they were the same thing.

4. Never TIMESTAMP

Two reasons, each enough on its own: the year 2038, and the hidden conversion by the connection's zone, which makes what you store and what you get back differ depending on who is asking. DATETIME converts nothing, it stores what it gets and returns what it stored. That doesn't end the discipline around zones, it just moves it to you: the convention prescribes neither UTC nor local time, both are legitimate, what it prescribes is consistency. The whole database in one zone, conversion only at display time. Both DEFAULT CURRENT_TIMESTAMP and NOW() insert the time in the session's zone, so once the server's default_time_zone drifts apart from the zone of your historical data, new rows quietly get a different zone than the old ones and nothing reports it.

5. “X changed” is not “something changed”

Two columns that look the same and aren't. The deciding question: what is this column supposed to measure?

  • modified_at measures a domain change, that is, “the date of the last edit from the reader's point of view”. Flipping a flag or recalculating a view counter must not move it. ON UPDATE CURRENT_TIMESTAMP is therefore wrong here, and the column has to be maintained by a trigger that watches only the columns you name.
  • updated_at measures that something changed. It's a row version, a token against concurrent editing. Here ON UPDATE CURRENT_TIMESTAMP is exactly right and a trigger would be worse: it would have to enumerate every editable column and would silently go stale when an eleventh one is added. Protection against overwriting somebody else's edit would quietly stop covering the new fields.

The trigger for the first case watches a single column, content, and everything that matters is in the comments:

DELIMITER ;;

-- BEFORE, because SET NEW.* has an effect nowhere else
CREATE TRIGGER `page_before_update_touch_modified_at`
BEFORE UPDATE ON `page` FOR EACH ROW
BEGIN
    -- COLLATE bin on both sides: without it, fixing accents doesn't count as a change (rule 11)
    IF NEW.content COLLATE utf8mb4_0900_bin <> OLD.content COLLATE utf8mb4_0900_bin
       -- <=> is NULL-safe equality; stops the trigger from overriding a date set by hand (migration, import)
       AND NEW.modified_at <=> OLD.modified_at THEN
        SET NEW.modified_at = CURRENT_TIMESTAMP;
    END IF;
END;;

DELIMITER ;

From which follows a more general duty: every magically maintained column needs a COMMENT naming what fills it. For updated_at especially (“concurrency token, the ON UPDATE is intentional”), otherwise the next reader will “fix” it into a trigger in good faith and quietly break optimistic locking.

6. A boolean is an adjective

For a while I considered making the is_ prefix mandatory, until it hit me that I was contradicting myself: I ban Hungarian notation like str_title on the grounds that the type belongs in the schema and not in the name, and is_ encodes nothing but the type. The opposite extreme doesn't hold up either. Try inventing an adjective for food_supplement or spam — the domain talks about them with nouns, and forcing them into adjectives means inventing words nobody would say. So the rule isn't the shape of the name, it's a procedure:

  1. Verify it should be a boolean at all. A state where the moment matters belongs in a timestamp (read_at carries both for free). More than two states belong in an enumeration. And a value you can compute from another column needs no storing at all: whether a category is a subcategory is something you tell from it having parent_id filled in.
  2. Try an adjective or a participle: published, visible, pinned. Always positive polarity, because WHERE NOT disabled is a brain-teaser.
  3. When no adjective fits, look at the thing differently: use_avatar → avatar_enabled, hide_price → price_visible, noindex → indexable.
  4. Otherwise is_. That's not a failure, it's a legitimate fallback. Permissions then always get can_.

Why a procedure rather than a shape? Because hunting for an adjective is diagnostic. I went through a hundred booleans across ten databases and tried to find a prefix-free name for each. Out fell seven columns that shouldn't have been booleans at all, two derivable ones (and therefore ready for deletion), and a handful of cases of inverted polarity. A mechanical prefix would have set all of that in concrete.

The second argument is that the main surface isn't SQL, it's the template:

{if $user->avatarsVisible}    reads like a sentence
{if $user->isShowAvatars}     ???

isXxx is the shape of a method across the whole ecosystem, so a property with that name is a visual false friend and it closes the door on splitting into a property active and a getter isActive().

The column is then always tinyint(1) NOT NULL with a default whose value follows the semantics of the safe state: enabled can happily be DEFAULT 1. A name isn't flipped for the aesthetics of a zero.

7. VARCHAR(255) is cargo cult

That number comes from historical limits: the one-byte length prefix worked up to 255 bytes, and the old key limit was 767 B. In utf8mb4 neither holds. 255 characters is up to 1020 bytes (and the prefix is two bytes anyway), and the key limit today is 3072 B.

Today 255 optimises precisely nothing. It's just a number we once saw in somebody else's table.

A length is a declaration of the domain and strict mode enforces it. The best numbers are the ones you can defend with a standard: email varchar(254) per the RFC, country char(2) per ISO 3166, iban varchar(34) per ISO 13616.

For plenty of columns, though, no standard exists, and I have to admit that slug varchar(100) is cargo cult too, just a generation younger. A hundred characters comes from nothing but a feeling that a longer address is nonsense. The difference from 255 is a single one, and a substantial one: with that hundred you know why you picked it. It's a ceiling you set deliberately, not a number copied from someone else's table. The point of a ceiling is to give the data a limit and cut off junk, not to hit a magic constant.

Careful with TEXT: it's 64 KB of bytes, not characters. For HTML article content go straight for MEDIUMTEXT, 64 KB can get surprisingly tight.

And when the set of values is small and fixed, ENUM is no mistake. It takes one byte in every index, an admin can read the allowed values out of information_schema and build a dropdown from them, and appending a value is ALGORITHM=INSTANT. Its price lies elsewhere: ORDER BY sorts by internal position rather than by text, and WHERE status = 1 compares against the position, not the value. And renaming goes “expand, migrate, contract”, as I described earlier.

8. Money is DECIMAL

FLOAT and DOUBLE are banned for anything that gets calculated or compared exactly. Binary approximation, errors accumulating in SUM(), = 0.3 not working. Leave floats for measurements where approximation is the whole point: a sensor, coordinates.

The more interesting dispute is DECIMAL versus integer cents. In PHP, keeping an amount as an int multiplied by a hundred is perfectly sensible, because the language has no exact decimal type. But what's good in the application isn't automatically good in the column: in the database, cents carry the interpretation out of the schema, and one day somebody prints the value without dividing by a hundred. The ideal division of labour is therefore DECIMAL in the schema and integers or a money object in PHP, with the conversion in one place. The driver hands you DECIMAL as a string precisely because PHP has no exact type; don't cast it to float, that throws away the very thing the column bought you.

Pick the scale by domain, not out of generic caution: DECIMAL(9,2) for amounts, (9,4) for unit prices and rates, (12,6) for exchange rates. Handle non-negativity with a CHECK, not UNSIGNED, which has been deprecated on DECIMAL since 8.0.17.

And a trap not even strict mode warns you about: inserting a value with higher precision gets silently rounded, with nothing but a warning. Rounding is a domain decision, so make it deliberately. Incidentally, ROUND in MySQL is half away from zero; banker's rounding doesn't exist here.

9. ON DELETE follows the nature of the relationship

Foreign keys explicitly, with both clauses. ON UPDATE CASCADE as the default. With surrogate keys it practically never comes into play, because id doesn't change, so it costs you nothing. But the moment renumbering really does happen (merging data by hand, aligning ids across environments), it performs it atomically instead of driving you toward SET FOREIGN_KEY_CHECKS=0. That's the real danger, because with the check disabled the references don't get updated and you end up with orphans.

ON DELETE has exactly one deciding question: is the child a part of the parent, or an independent thing that merely points at it?

relationship rule example
composition CASCADE order_item → customer_order, pivots
association RESTRICT post.author_id → user
meaningful detachment SET NULL log.user_id after anonymisation

SET NULL is the worst possible default. The reference evaporates, the child stays dangling, and on top of that it forces you into a nullable column. Use it only where an orphaned child still makes sense and that NULL means something.

Two traps will then catch you elsewhere. Cascading deletes don't fire triggers, because InnoDB handles them below the SQL layer. An invariant maintained by a trigger is therefore silently bypassed by a cascade. And a cascade only exists for hard deletes: with a soft delete no real DELETE ever arrives and your carefully designed cascade is dead code.

10. CHECK for invariants, not for policy

Let's start with the fact that CHECK constraints in MySQL exist and you want them. Up to version 8.0.16 the server parsed them and silently ignored them, which is probably why half the world still considers them a PostgreSQL privilege and the other half doesn't trust them. Today they're enforced, so it's a full-blown tool for moving a domain rule out of the application and into the schema.

The question then is what belongs in one. The axis is this: domain invariant versus business policy. An invariant is a timeless truth (price >= 0, the end isn't before the beginning) and belongs in the schema. A policy is a changeable rule (“orders above 10,000 need approval”), it'll change within a year and a CHECK would mean a migration.

The best use is what no other mechanism can express: relationships between columns of the same row, and conditional obligation, which is the implication A -> B written as NOT A OR B:

CONSTRAINT `chk_order_shipped_needs_date`
    CHECK (`status` <> 'shipped' OR `shipped_at` IS NOT NULL),

Notice the name. Name constraints after the rule, not after the columns, because this name is what the user sees in the error message. The test: you read the name in a log and know what happened without opening the schema.

What a CHECK can't do: anything across rows (it has no subqueries), anything non-deterministic (CURDATE() is forbidden, so birthdate <= CURDATE() is out), and it must not reference an AUTO_INCREMENT column. And don't add a redundant CHECK where a cheaper mechanism already guarantees the same. CHECK (age >= 0) on a TINYINT UNSIGNED is just noise plus a cost on every write.

The title promised ten and this is where the article should properly have ended. But the three rules that remain are among those whose violation hurts the longest, and throwing them out over a round number in the headline would be exactly the kind of decision I've been criticising all along: going for looks instead of reasons. So, eleven. And twelve. And while we're at it, thirteen.

11. Collation: 0900, or your CHECK is just decoration

utf8mb4 always, never utf8 (that's the three-byte alias that breaks emoji). You probably know that. Less well known is that the collation belongs to the utf8mb4_0900_* family, that is UCA 9.0. The legacy utf8mb4_czech_ci, unicode_ci (UCA 4.0.0!), general_ci and utf8mb4_bin have no reason to exist in a new schema.

And now the important part, because you don't learn this from the documentation but from production. Comparisons and REGEXP inside a CHECK respect the column's collation. Under the default _ai_ci this constraint happily lets through 'CS' and 'cs_CZ':

CHECK (`lang` REGEXP '^[a-z]{2}$')

The part of the regular expression that distinguishes case does absolutely nothing there. A format or membership CHECK only makes sense over a column with _bin, ascii_bin or _as_cs collation.

The same trap bites in the trigger from rule 5. Without that COLLATE utf8mb4_0900_bin, the default collation wouldn't consider fixing “cafe” into “café” a change at all. Adding accents — a routine edit in most of the world's languages — wouldn't move the last-modified date one bit.

12. An ascii column returns a 500 instead of a 404

Almost every schema has a few columns that by definition hold nothing but ascii: an article slug, a coupon code, a login name, an API token. CHARACTER SET ascii suggests itself as the clever choice, because in a key it reserves one byte per character instead of four. Except that a charset isn't chosen by what sits in the column, but by what it gets compared against. And those columns have one more thing in common: they're precisely the ones you search by, so the value in the condition doesn't come from your table, it comes from outside. And out there anyone can send you anything.

-- slug varchar(100) CHARACTER SET ascii
SELECT * FROM post WHERE slug = 'creme-brulee';  -- fine
SELECT * FROM post WHERE slug = 'crème-brûlée';  -- ERROR 1267 Illegal mix of collations

The connection is utf8mb4, so the value from outside arrives as utf8mb4. As long as it contains only ascii characters, MySQL silently converts it and compares. The moment it contains a single character beyond that, conversion isn't possible and the query ends with an error, not with an empty result. That's the whole difference: a value that isn't in the table normally just isn't found, whereas this query never gets to ask.

So the application never gets its chance to return a 404 and the visitor gets a 500 because they typed an accent into the address. Tests won't catch it either, they try existing and non-existing values, not values with accents.

I verified this on MySQL 8.4, and it isn't _bin that does it, it's the ascii charset: every string operator fails, from = to CONCAT, and what decides is the content of that string rather than the data in the table, so an empty table fails just the same. You can work around it with CONVERT(? USING ascii), but it has to be on every query, and the first place you forget brings the trap back.

So the question isn't “is this data ascii?”, but “can a non-ascii value reach the comparison?”. Keep ascii for columns filled exclusively by the application that nobody queries from outside (lang, country, ip_address). Everything else gets utf8mb4_0900_bin, which does the same job and only takes more room in the index. A wrong ascii choice is an outage; a wrong utf8mb4 choice is a few extra bytes.

13. The primary key propagates

INT UNSIGNED AUTO_INCREMENT as the default primary key, BIGINT for tables you only ever pour into.

Why is the primary key the one place where saving bytes pays off in a way that would be premature optimisation anywhere else? Because in InnoDB the PK is physically part of every secondary index and has to be type-identical in every foreign key pointing at it. You don't pay for its size once, but (1 + number of indexes + number of references) times.

Two things routinely get overlooked:

  • AUTO_INCREMENT follows the maximum, not the row count. It never recycles, and nobody will ever fill the holes left by rollbacks or INSERT IGNORE. A queue or a log will exhaust INT with a few million live rows. Look at how many rows will be inserted over the table's lifetime, not how many will be sitting there.
  • The risk is asymmetric. Under-sizing is a production incident: INSERTs fail and ALTER to BIGINT is a long heavy operation at the worst possible moment. Over-sizing costs four bytes per row. When in doubt, take BIGINT.

UNSIGNED is worth it on INT, where doubling to 4.29 billion often settles the question of whether you need BIGINT at all. On BIGINT it isn't: the signed range is already absurd and the upper half is unreachable from PHP anyway.

The rest is hygiene

Things that don't deserve a rule of their own but will eat an afternoon if you skip them:

  • Always name indexes and constraints yourself. Otherwise MySQL produces post_ibfk_1 and post_chk_1, names numbered by order of creation. All it takes is for someone to drop a constraint and add it back, or for the table to be built from a dump elsewhere instead of from migrations, and the numbering diverges. DROP FOREIGN KEY post_ibfk_2 then passes in one environment and fails in another — or drops something else entirely.
  • Name them hybrid: indexes without a prefix, constraints with one (fk_post_blog, chk_post_lang_format). The difference has a reason: a constraint's name is part of the error message, so it belongs where the reader meets it, whereas an index name is only ever read by whoever is tuning queries.
  • Technical index, or performance index? The one under a foreign key and the UNIQUE on a natural key exist from day one; those aren't optimisations, they're constraints. The rest are born from a real query, from EXPLAIN and the slow log. Name them accordingly: a technical one after its columns, because its content is its purpose, a performance one after the query it serves (post_listing).
  • An index no query uses is pure cost. It slows down every write and eats buffer pool.
  • Leftmost prefix: an index (a, b, c) serves conditions on a, a+b and a+b+c, but not on b alone. Hence the recipe: equality columns first, then at most one for a range or ordering. Beyond the range column the index stops filtering.
  • In InnoDB the PK is implicitly at the end of every secondary index. So don't repeat it there, a covering index often comes for free, and it's the main reason behind rule 13.
  • Don't index a low-cardinality column on its own, a boolean filters nothing out. As part of a composite in the right position it's perfectly fine.
  • An index (a) next to (a, b) is redundant, the leftmost prefix covers it.
  • An FK column must copy the target PK's type exactly, UNSIGNED included, or you won't create the foreign key at all.

When to break all of this

The only unbreakable parts are the rule-zero prerequisites (strict mode, utf8mb4, explicit foreign keys) and the facts about MySQL, because what can't be created can't be created. Everything else you may break with a reason that would hold up as a written decision: specific, recorded, and able to survive the question “why is this like this?” two years from now. “I don't like it” is not a reason. “This table has 500 million rows and we won't survive the rebuild” is.

Just make sure the deviation is visible where the reader will meet it, that is in the COMMENT of the column or the table. Not in a commit message, not in Confluence, and definitely not in your head. SHOW CREATE TABLE is the only documentation that travels with the data.

A convention without an escape hatch either gets bypassed in secret, or blocks the work 🙂

And why pour this much energy into the schema when it's faster to write it in the application? Because one day you'll rewrite the application. The data, never.