Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 75 ยท Tables, Constraints and Schema Design

Schema Design Patterns for MySQL

Practical MySQL schema design: normalization trade-offs, surrogate vs natural keys, soft deletes, audit columns, many-to-many tables, the EAV trap and JSON vs columns.

A schema is the part of an application that is hardest to change later. Code can be refactored in an afternoon, but a table with 200 million rows that every service reads is a long-term commitment. A few well-known patterns, and a few well-known traps, cover most of the decisions you will face.

This lesson is a tour of those decisions as they apply to MySQL and InnoDB: how far to normalize, which kind of key to use, how to model deletes and history, how to link tables, and when to reach for JSON. Most examples run live in the SQLite stand-in. Where a pattern needs MySQL-only syntax, the block is read-only and says so.

Questions to ask for every table

What is the key? Surrogate, natural or composite.
What does it link to? One-to-many or many-to-many.
What happens over time? Deletes, edits and history.
How will it grow? Think about 100x the data.
1 / 2

Normalize first, then bend the rules on purpose

Normalization means storing each fact once. A customer's address lives in a customers table, and orders refer to the customer by key. When the address changes you update one row, and every report sees the change. A normalized schema is easier to keep correct, because constraints can protect each fact.

Denormalization copies data to make reads cheaper: an order_count on the customer, a category_name copied into each product row, a pre-computed total on an order. It is a legitimate tool, with one rule: you accept the job of keeping the copy in step.

A sound approach for MySQL applications:

  1. Design to third normal form: every non-key column describes the key, the whole key and nothing but the key.
  2. Measure. Find the queries that are really slow, and check that the right indexes exist (How MySQL Indexes Work comes later).
  3. Only then denormalize a specific column, and add a mechanism to keep it right, such as a trigger or a nightly job, and write down why it exists.

Order totals are a good example where a copy is correct. The price on an order line must not follow the product's current price: the customer paid the old price. Copying the price into order_items at purchase time is not denormalization at all, it is recording a historical fact.

Surrogate or natural primary keys

A natural key is a value from the real world that is already unique: an ISBN, a country code, an email address. A surrogate key is a meaningless identifier your database invents, usually an AUTO_INCREMENT integer.

Natural keys sound attractive but age badly. Emails change, product codes get reused, and a "unique" national ID turns out not to be unique across countries. A key that appears in many child tables and in every secondary index (remember, InnoDB copies the primary key into each one) is expensive to change and expensive to store when it is long.

The pragmatic default for MySQL is therefore:

  • A narrow surrogate primary key, BIGINT UNSIGNED AUTO_INCREMENT (or INT UNSIGNED when the table will stay small).
  • A UNIQUE constraint on the natural key, so the database still prevents duplicates: UNIQUE (isbn).
  • Natural keys as primary keys only for small, stable lookup tables, such as a countries table keyed by its two-letter ISO code.

Link tables are the exception: they usually use a composite primary key of the two foreign keys, as shown below.

One-to-many

The most common relationship needs only a foreign key on the "many" side. One customer has many orders, so orders.customer_id points to customers.id. Always index the foreign key column (InnoDB does this for you when you declare the constraint, see Foreign Keys in MySQL), because you will constantly ask "all orders of this customer".

Many-to-many with a junction table

Students take many courses and courses have many students. A column cannot hold a list, so the relationship becomes its own table with one row per pairing. This is a junction table (also called a link or association table):

SQL A junction table for a many-to-many relationship

Press Run (or Ctrl + Enter) to see the result here.

The composite primary key (student_id, course_id) prevents the same pairing twice and doubles as an index for "courses of a student". For the opposite direction, "students of a course", you would add a second index on (course_id, student_id). Extra facts about the pairing, like grade here, live in the junction table too. In MySQL the table would use BIGINT UNSIGNED columns that match the parents exactly, and a column such as enrolled_at is a common addition.

Soft deletes

A soft delete keeps the row and marks it as deleted, usually with a nullable timestamp such as deleted_at. People use it for undo, for audits and to avoid cascading deletes across many tables.

Soft deletes have real costs, so adopt them deliberately:

  • Every query must remember WHERE deleted_at IS NULL. Forgetting it once is how deleted data reappears. Wrap the filter in a view (see Views) or in a default scope of your framework.
  • Unique constraints become awkward. If email is unique, a deleted user blocks that email forever.
  • The table keeps growing, and indexes carry dead rows. You still need a retention policy.
  • Privacy law may require real deletion anyway.
SQL Soft delete with a view, and the unique-email problem

Press Run (or Ctrl + Enter) to see the result here.

The view shows only Ada, and the final insert fails with UNIQUE constraint failed: customers.email. There are two ways out. You can include the deletion state in the key, for example UNIQUE (email, deleted_at), but a NULL in deleted_at makes two live rows with the same email all too possible, since NULL is never equal to NULL. Or you can put deleted rows in a separate archive table.

SQLite offers a third way, a partial unique index that applies only to live rows. MySQL has no partial indexes, but you can imitate one with a generated column that is NULL for deleted rows (a unique index ignores NULLs):

SQL
-- MySQL 8: read-only here
ALTER TABLE customers
  ADD COLUMN live_email VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
  ADD UNIQUE KEY uq_customers_live_email (live_email);
SQL SQLite partial unique index: only live rows must be unique

Press Run (or Ctrl + Enter) to see the result here.

This time the insert succeeds, because the deleted row is outside the index.

Audit columns and optimistic locking

Give most business tables four columns: created_at, updated_at, and, if you track users, created_by and updated_by. In MySQL the timestamps can maintain themselves (see CREATE TABLE in Depth). When a record's full history matters, use an audit or history table, which gets its own project later in the course.

A version column adds optimistic locking: an update succeeds only if nobody changed the row since you read it. Two people edit the same article; the second save finds that the version moved on, and your code can ask the user to reload:

SQL Optimistic locking with a version column

Press Run (or Ctrl + Enter) to see the result here.

Alice's update changes one row. Bob's changes zero rows, because the version is now 2. His application sees "0 rows affected" and knows there was a conflict. In MySQL the same check is the affected-rows count from the driver. (MySQL counts a row as affected only when its values really change, which is the right behavior here, since the version always changes.)

The EAV warning

Entity-attribute-value stores each property as a row: (entity_id, attribute, value). It looks flexible, because a new attribute needs no ALTER TABLE. In practice it is a trap:

  • Every value is text, so there are no real types, and no NOT NULL, CHECK or foreign key constraints.
  • Reading one entity needs a pivot, or one join per attribute.
  • Filtering on several attributes needs self-joins, which get slow quickly.
SQL EAV forces a pivot just to read one object

Press Run (or Ctrl + Enter) to see the result here.

Compare that to SELECT name, price, color FROM products. If your attributes really vary per row, use the next pattern instead.

JSON column or real columns?

MySQL's JSON type (covered in its own chapter) stores validated documents and lets you query inside them. Use a hybrid design: real columns for everything you filter, join, sort or constrain on, and a JSON column for the long tail of optional, varying attributes.

SQL Typed columns plus a JSON column for the rest

Press Run (or Ctrl + Enter) to see the result here.

SQLite's json_extract and ->> work the way MySQL's do, so the queries carry over. The last statement fails the json_valid check: CHECK constraint failed: json_valid(attrs). MySQL's JSON type checks validity for you and rejects malformed text with an error.

Rules of thumb:

  • A value you filter or join on every day belongs in a column. In MySQL you can still index a JSON path through a generated column (Generated Columns).
  • A value that is mostly displayed, never joined, and different per row fits JSON.
  • Nested lists in JSON cannot have foreign keys. If an item needs referential integrity, it needs a table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the pragmatic default primary key for most MySQL tables?

    A short surrogate key keeps every InnoDB secondary index small and never has to change. A UNIQUE constraint still protects the natural key.
  2. 2. How do you model a many-to-many relationship in a relational schema?

    A junction table holds one row for each pairing, with foreign keys to both sides and usually a composite primary key.
  3. 3. What is a problem with soft deletes and a UNIQUE email column?

    The row still exists, so the unique index still contains the email. Solutions are an archive table or a generated column that is NULL for deleted rows.
  4. 4. Why is the entity-attribute-value (EAV) pattern usually a bad idea?

    All values become generic text, so constraints cannot protect them, and reading one entity needs a pivot or a join per attribute.

Summary

  • Normalize first and denormalize only for measured reasons. Copying a price onto an order line is not denormalization, it is history.
  • Use a narrow surrogate primary key plus UNIQUE on natural keys, because InnoDB copies the primary key into every secondary index.
  • One-to-many needs an indexed foreign key. Many-to-many needs a junction table with a composite key.
  • Soft deletes need a filtering view, a plan for unique keys and a retention policy. In MySQL, imitate a partial unique index with a generated column that is NULL for deleted rows.
  • Audit columns and a version column give you timestamps and optimistic locking cheaply.
  • Avoid EAV. Use typed columns for what you query and a validated JSON column for the optional rest.

Next: AUTO_INCREMENT, UUIDs and ID Strategies, a closer look at how the keys get their values.