MariaDB Tutorial / Normalize an Order Database Without Losing Historical Facts

Normalization analyzes which keys determine which facts and separates tables to reduce repeated current information and update anomalies. Distinguishing current customer or product attributes from purchase-time facts allows a current name to change in one place while preserving an order’s historical unit price.

Find anomalies caused by repeating the same fact

Copying a customer’s current name into every order item can leave that customer with different names after a partial update. Deleting the last item can also remove the only stored customer information, and a new customer may have no place to be stored before ordering. Separating the subjects of those facts reduces update, deletion and insertion anomalies. Verify actual key/foreign-key definitions using Design Table Relationships with Keys and Foreign Keys; Change Table Structure Safely with ALTER TABLE applies new conditions to existing data in explicit stages.

Atomicity depends on the domain and intended use. A comma-separated product list is unsuitable when individual product quantities must be queried, but this does not mean every text or JSON value must always be decomposed.

Check the MariaDB documentation for the syntax and conditions used here: database normalization 3rd normal form, foreign keys.

Create the normalized fixture and a flattened comparison

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c37_customers, c37_products, c37_orders, c37_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c37_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
  REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_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),
 CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
  REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
  REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Also check that c37_flat does not exist. CREATE TABLE AS SELECT creates a comparison copy for observing repetition; it does not automatically reproduce all source keys and constraints and is not a complete backup.

CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
 p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;

Customer 1’s name appears on three item rows. Updating only order 101’s copies makes the old and changed names coexist for the same customer. ROLLBACK cancels that comparison-table modification; the normalized customer table was not changed.

Trace partial and transitive dependencies

With an order_id/product_id composite item key, the order date depends only on order_id. Repeating it on every detail stores a fact dependent on only part of the key. Keeping it in orders separates that order fact from the item quantity and transaction price.

An order identifies its customer, and the customer identifies the current customer name. Repeating that current name on every order spreads a customer update across many places. Referencing customers by customer_id provides one current-name authority. These dependency questions give practical meaning to reviewing second and third normal forms.

Preserve historical prices as a different fact

Do not remove items.unit_price merely because it currently equals products.price. One records the purchase-time price and the other the current sale price. They refer to different facts in time; historical order totals need the transaction value after the catalog price changes.

Shipping addresses or display names can likewise require explicit purchase-time snapshots. Give current attributes and historical evidence distinct meanings, capture times and update policies. Preserving necessary history differs from duplicating an undefined current fact.

Enforce the intended keys and business conditions

Creating four tables alone does not reject invalid references. Define PRIMARY KEY, UNIQUE, NOT NULL and foreign keys, then test missing-parent and duplicate-key inputs. The relationship and foreign-key exercise demonstrates those failures.

This simplified model combines one product into one item row within an order. Options, batches or partial shipments may need a separate item identifier and additional keys. Adapt the row meanings to requirements rather than treat the fixture as the complete answer for every order system.

Measure before introducing deliberate denormalization

Normalization establishes an update-consistency baseline; query speed depends on actual queries, indexes and data volume. If a reporting summary table is needed, define its authoritative source, refresh rules and error recovery. Many JOINs alone are not a reason to repeat current information without a maintenance policy.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;