MySQL / Relational foundations and precise SQL / Lesson 1

Relational thinking and the shop laboratory

Build a precise model of customers, orders, products, and historical order lines before writing reports.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Explain rows, candidate keys, foreign keys, and relationship cardinality.
Read the complete fixture and predict its row counts without executing it.
Separate MySQL 8.4 assumptions from MariaDB compatibility claims.

A row states one fact

A stationery shop needs to remember who purchased which products and what each item cost at purchase time. A table represents a kind of fact; a row is one occurrence, and a column describes an attribute. An order belongs to one customer, while one customer can have many orders. An order can contain many products, and a product can appear in many orders. The order_items table represents that many-to-many relationship explicitly.

Identity and historical truth

A primary key identifies a row independently of its display name. Names can repeat, so customer id is the reference carried by orders. The composite order-line key permits one line per product per order. Storing unit_price on each line preserves the agreed price if the product catalogue changes tomorrow. The order total is a deliberate duplicate summary: a later reconciliation exercise will check it against the lines rather than assuming both always agree.

Complete synthetic setup

This course targets the supported MySQL 8.4 LTS series, using InnoDB and normal strict SQL modes including ONLY_FULL_GROUP_BY. Review the exact patch and client compatibility before a real installation. The following script belongs only in a new disposable database named mysql_course_lab. It neither connects to a server nor runs in this public lesson. All later examples assume these unchanged rows unless they declare their own local fixture. No credentials or real customer records are needed.

sql A complete training fixture

CREATE DATABASE mysql_course_lab CHARACTER SET utf8mb4;
USE mysql_course_lab;
CREATE TABLE customers (
 id INT PRIMARY KEY, name VARCHAR(80) NOT NULL,
 email VARCHAR(120) NOT NULL UNIQUE, city VARCHAR(40) NULL
) ENGINE=InnoDB;
CREATE TABLE products (
 id INT PRIMARY KEY, title VARCHAR(80) NOT NULL,
 price DECIMAL(10,2) NOT NULL, stock INT NOT NULL,
 CHECK (price >= 0), CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE orders (
 id INT PRIMARY KEY, customer_id INT NOT NULL,
 status VARCHAR(12) NOT NULL, placed_at DATETIME NOT NULL,
 total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
 FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;
CREATE TABLE order_items (
 order_id INT NOT NULL, product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;
INSERT INTO customers VALUES
 (1,'Amina','amina@example.test','Cairo'),
 (2,'Leo','leo@example.test','Lisbon'),
 (3,'Noor','noor@example.test',NULL);
INSERT INTO products VALUES
 (1,'Notebook',10.00,20),(2,'Pen',2.50,100),(3,'Bag',30.00,5);
INSERT INTO orders VALUES
 (101,1,'paid','2026-01-10 10:00:00',25.00),
 (102,1,'new','2026-01-11 11:00:00',30.00),
 (103,2,'paid','2026-01-12 12:00:00',10.00);
INSERT INTO order_items VALUES
 (101,1,2,10.00),(101,2,2,2.50),(102,3,1,30.00),(103,1,1,10.00);

Create parent tables before their children, then insert parent rows before foreign-key references. The fixture contains three customers, three products, three orders, and four lines. The two lines of order 101 total 2 × 10.00 + 2 × 2.50 = 25.00. Explicit identifiers make every subsequent result reproducible.

Inspect meaning before volume

The catalogue price and historical line price answer different questions. Missing city means unknown information, represented by SQL NULL, rather than an empty string or the literal text NULL. Keys and constraints protect relationships even when several applications write concurrently. They do not prove that an email belongs to a person or that payment happened. Those are separate business processes requiring evidence outside this small relational model.

sql Read the customer relation

SELECT id, name, city FROM customers ORDER BY id;

Expected output

id | name | city
1 | Amina | Cairo
2 | Leo | Lisbon
3 | Noor | NULL

The explicit ordering produces Amina, Leo, then Noor. Noor has SQL NULL in city. Without ORDER BY, the server promises no row order even if the primary-key index happens to be scanned. A table is a relation, not a spreadsheet with permanent row positions.

Know the actual server

MySQL and MariaDB share SQL ancestry but have separate release lines, optimizers, authentication, JSON implementations, and replication details. Do not treat a MariaDB version string as proof of MySQL 8.4 behavior. Learn the model here, then verify vendor-specific syntax against the actual server manual. Examples are non-runnable teaching material; their displayed results are reasoned from the fixture, not measurements from a hidden production connection.

Common errors and fixes

Duplicate identity: ERROR 1062 (23000).

Why it happens: Reusing customer id 1 collides with the primary key.
How to fix it: Choose a genuinely new id or intentionally update the existing customer.
Prevent it next time: Keep identity allocation separate from names and verify import mappings.

Missing parent: ERROR 1452 (23000).

Why it happens: An order references customer 99, which does not exist.
How to fix it: Create the legitimate parent first or correct the mistaken reference.
Prevent it next time: Load dependent tables in relationship order; retain foreign-key checks.

Running setup twice: ERROR 1007 (HY000).

Why it happens: The named training database already exists.
How to fix it: Use a separately named disposable lab after reviewing its contents.
Prevent it next time: Treat setup as a one-time script and never drop an unfamiliar database.

Check your understanding

1. Why cannot orders store a comma-separated list of product ids?

Show worked solution

Each product needs its own quantity and historical price, and foreign keys must validate individual references. Four order_items rows express those independent facts. A text list would force parsing and make uniqueness and integrity harder to enforce.

2. Predict the order ids belonging to customer 1 and explain the relationship.