MySQL / Relational foundations and precise SQL / Lesson 6

Types, normalization, and schema evolution

Choose representations that preserve meaning and plan schema changes without assuming DDL is ordinary transactional DML.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Choose exact numbers, text, and temporal types deliberately.
Recognize normalization tradeoffs and compatible migration steps.

Represent the domain accurately

The shop fixture separates customers, orders, products, and lines because they have different identities and lifecycles. Repeating customer email on every order creates an update anomaly: one address change requires many writes. Normalization places each independently maintained fact with its key. Deliberate snapshots, such as a historical shipping address, are different facts and may legitimately remain on an order.

Exact and approximate numbers

Use DECIMAL for fixed-scale monetary examples; FLOAT and DOUBLE approximate binary values and can introduce surprising equality results. Precision counts total digits and scale counts fractional digits: DECIMAL(10,2) permits eight integer digits and two fractional digits. Choose integer ranges from expected growth, not current fixture size. Consistent signedness matters when relating integer foreign keys to referenced columns.

sql Explicit precision and scale

SELECT CAST(0.10 AS DECIMAL(10,2))
     + CAST(0.20 AS DECIMAL(10,2)) AS amount;
SELECT CAST(123.456 AS DECIMAL(6,2)) AS rounded;

Expected output

amount
0.30
rounded
123.46

The first expression returns exactly 0.30 using decimal operands. The second returns 123.46, demonstrating that declared scale can round a supplied value. Validate business rounding rules before storage rather than assuming every extra decimal place will be rejected.

Text equality is a policy

VARCHAR length is a character limit; encoded bytes and index limits remain separate concerns. Use utf8mb4 for full Unicode, including Arabic and supplementary characters. A collation determines comparisons and sorting, including case and accent sensitivity. A unique email constraint therefore follows its column collation, not a universal email-identity rule. Define normalization in the application and choose a compatible database comparison deliberately.

Time and missing values

DATE represents a calendar date; DATETIME represents a supplied date and time; TIMESTAMP converts through the session time zone when storing and retrieving. Choose and document a UTC convention for instants, and preserve a separate business zone when calendar interpretation matters. NULL is useful only when unknown or inapplicable is a valid domain state. NOT NULL alone does not prohibit empty text or zero.

sql Choose meaning before representation

SELECT DATE('2026-01-10 10:00:00') AS order_day;
SELECT COUNT(*) AS customers, COUNT(city) AS known_cities
FROM customers;

Expected output

order_day
2026-01-10
customers | known_cities
3 | 2

DATE extracts 2026-01-10 from the literal. The customer query returns three customers but only two known cities. Storing an empty string for Noor would increase COUNT(city) without adding real geographical knowledge, changing the meaning of reports.

Evolve in compatible stages

For a new required attribute, first add a nullable column, deploy code that can read old and new rows, backfill in bounded batches, validate completeness, then enforce NOT NULL. Assess metadata locking, table size, and the supported ALTER algorithm before production changes. MySQL atomic DDL improves crash behavior for supported operations but does not make CREATE TABLE or ALTER TABLE rollbackable by a surrounding application ROLLBACK.

Common errors and fixes

ERROR 1264 (22003): Out of range value.

Why it happens: A strict-mode write exceeds the selected numeric range.
How to fix it: Correct the invalid value or perform a reviewed widening migration.
Prevent it next time: Estimate future domain limits and validate boundaries before deployment.

ERROR 1406 (22001): Data too long.

Why it happens: Input exceeds the VARCHAR character limit in strict mode.
How to fix it: Reject or deliberately expand the domain; do not silently truncate names.
Prevent it next time: Align application validation with database character limits.

A schema change survives ROLLBACK.

Why it happens: DDL caused an implicit commit and is not ordinary rollbackable DML.
How to fix it: Use an independently reviewed reverse migration or restore strategy.
Prevent it next time: Review DDL commit and locking semantics before rehearsals.

Check your understanding

1. Choose representations for product price, customer name, and an unknown city.

Show worked solution

Use DECIMAL(10,2), VARCHAR(80) with utf8mb4, and a nullable VARCHAR(40) city respectively in this fixture. The choices preserve exact cents and Unicode names while distinguishing absent city information. They are domain decisions, not universal sizes for every shop.

2. Explain why line unit_price should not be replaced by a join to the current catalogue price.