Retail Database Design (SQL)

About this project

A properly normalised database for a small retail shop — customers, products, categories, orders, order lines and payments — with a seed dataset and a file of worked queries you run and read.

The idea it is built around is that normalisation prevents lies. The README starts with one wide spreadsheet-shaped table holding an order and shows the three things that go wrong with it: a customer whose phone number is stored in forty rows and now disagrees with itself, a product that cannot exist until somebody orders it, and deleting the last order of a product erasing the product. Then it takes the same data to 3NF and those three become impossible rather than merely discouraged.

The second theme is that the constraint is the guarantee. The queries file deliberately attempts thirteen bad writes and shows the exact error the database returns each time — because that error, not a comment in your application code, is what actually stops bad data. The demonstrations run inside transactions that roll back, with an assertion at the end proving the row counts are unchanged.

What you will learn

• Take one wide spreadsheet-shaped table to 3NF, and see each anomaly become impossible
• Use FOREIGN KEY, UNIQUE, CHECK and NOT NULL as guarantees the application cannot bypass
• Read EXPLAIN, add an index, and read it again to see what changed
• Store money as DECIMAL and know exactly what FLOAT would cost you
• Understand why an order line records the price at the time of sale, not a join to today’s price

Before you start

• Basic SQL — SELECT, INSERT, and what a table is
• MySQL 8.0.18 or newer. Not MariaDB: CHECK constraints are only enforced from 8.0.16.

New to SQL? The free SQL course covers everything this project assumes.

Features

• Seven tables in 3NF with foreign keys, unique keys, CHECK constraints and a generated column
• Thirteen deliberate errors, each showing the exact refusal MySQL returns
• Every demonstration wrapped in a transaction that rolls back, with a proof it did
• The same data as one wide table alongside, so you can compare them query for query
• EXPLAIN before and after an index, plus EXPLAIN ANALYZE for what actually happened
• A realistically shaped seed: 30 products, 12 customers, 45 orders, 130 order lines

Technology stack

Track: Data & Analytics · Level: Starter

What is included

• schema.sql, seed.sql and queries.sql — commented throughout
• A README on the three anomalies, the road to 3NF, and why money is DECIMAL
• A troubleshooting section, and five suggested extensions

Delivered as a 42 KB ZIP. SHA-256 published under Changelog so you can verify it.

System requirements

Operating system

Any

Runtime

MySQL 8.0.18 or newer. Not MariaDB — the CHECK demonstrations need real CHECK support.

Licence

Frequently asked

Changelog