Normalisation is a sequence of rules for arranging columns so that a fact is stored once. Each form removes a specific kind of anomaly, and they are easier to remember as problems than as definitions.
First normal form says each column holds one value and there are no repeating groups. A phone_numbers column containing "020 7123, 07700 900" fails it, and so does a table with item_1, item_2, item_3. The fix is a row per value. The symptom of breaking it is that you cannot query the data without string functions.
Second normal form applies when the key is made of several columns, and says every non-key column must depend on the whole key. In an order_items table keyed on (order_id, product_id), a product_name column depends only on the product, not on the pair - so it belongs in products. The symptom is the same name repeated on every row that mentions that product.
Third normal form says non-key columns must not depend on each other. A customers table with postcode and city has a problem, because the city follows from the postcode. The symptom is that correcting a postcode leaves the city wrong.
The practical rule: normalise to 3NF by default. Denormalise deliberately and later, with a measurement in hand, because every denormalised copy is a thing that can drift - and for reads that need speed, an index or a summary table is usually the better answer.
-- NOT 1NF: a repeating group in one column. You cannot query this
-- without string functions, and you cannot index it usefully.
-- CREATE TABLE customers (id INT, name VARCHAR(80), phones VARCHAR(255));
-- (1, 'Ada', '020 7123 4567, 07700 900123')
CREATE TABLE customer_phones (
customer_id INT UNSIGNED NOT NULL,
phone VARCHAR(32) NOT NULL,
kind ENUM('home','mobile','work') NOT NULL DEFAULT 'mobile',
PRIMARY KEY (customer_id, phone),
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE = InnoDB;
-- NOT 2NF: product_name depends on product_id alone, not on the whole
-- key, so it is repeated on every line that mentions the product - and
-- renaming the product means finding every one of them.
-- CREATE TABLE order_items (
-- order_id INT, product_id INT, product_name VARCHAR(160), qty INT,
-- PRIMARY KEY (order_id, product_id));
CREATE TABLE order_items (
order_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
quantity SMALLINT UNSIGNED NOT NULL,
-- Price IS stored here, and that is not a violation: it is the price
-- at the time of the order, which is a different fact from the price
-- on the product today.
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
) ENGINE = InnoDB;
-- NOT 3NF: city follows from postcode, so correcting one leaves the
-- other wrong. A postcode lookup table fixes it.