MariaDB講座 / 注文データベースを正規化し、購入時の事実を残す
正規化は、どのキーがどの事実を決めるかを整理し、繰り返す現在情報と更新の不整合を減らす設計です。現在の商品属性と購入時の事実を分ければ、名前や価格を更新しても過去の注文金額を保てます。
繰り返した事実の不整合を見つける
顧客の現在名をすべての注文明細へ複写すると、一部だけ更新して同じ顧客に異なる名前が残り得ます。最後の明細を消すと顧客情報も失われ、未注文の顧客を保存できない場合もあります。更新・削除・追加の異常を、事実の所属で減らします。
customer_idが現在名と都市、product_idが現在の商品名と価格、order_idが注文先顧客・時刻・状態、注文IDと商品IDの組が数量・購入時単価を決めます。主キーと外部キー、既存表の変更もこの定義に基づきます。原子性は用途によるため、数量検索が必要な商品リストを一文字列へ入れる問題を、すべてのJSONや文章を常に分解すべきという規則へ一般化しません。
正規化した表と平坦なコピーを作る
course_userでshopcourseに接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下は独立した実習表の作成・追加を含む完全な例で、UTF-8ファイルとして実行できます。同名表があれば定義と行を調べ、必要なものを保存してから判断します。
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);
文字列は原文の韓国語サンプルを維持しています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、安定したIDと数値で照合します。
c37_flatも未使用か確認します。CREATE TABLE AS SELECTは比較用コピーを作りますが、元のすべてのキーや制約を自動複製する完全なバックアップではありません。
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;
顧客1の名前は三明細に繰り返されます。注文101のコピーだけを変更すると、同じ顧客へ旧名と変更名が共存します。ROLLBACKは比較表の変更を取り消し、正規化した顧客表は変えていません。
部分従属と推移的な従属を考える
明細の複合キーのうち注文日がorder_idだけで決まるなら、その日付は明細ごとでなくordersへ所属させます。注文が顧客を決め、顧客が現在名を決めるなら、現在名を各注文へ繰り返さずcustomer_idで参照します。
第二・第三正規形は、こうしたキーと事実の従属関係を確認する視点として使えます。単に表が四つあるから正しいのではなく、PRIMARY KEY、UNIQUE、NOT NULL、外部キーと不正入力の拒否も必要です。
現在価格と購入時価格は別の事実
items.unit_priceが現在products.priceと同じでも、重複だからと削除しません。前者は購入時、後者は現在の販売価格で、意味と時点が違います。過去の合計は明細単価から再現します。
配送先住所や表示名も購入時スナップショットが必要なことがあります。現在属性と証拠の意味、取得時刻と更新規則を明示します。必要な履歴を残すことは、定義のない現在情報を重複させることとは違います。選択肢、ロット、分割発送があれば明細IDや追加キーも検討します。
非正規化は維持規則と実測で判断する
正規化は更新整合性の基準で、速度はデータ量、索引と問い合わせで決まります。集計表を持つなら正本、更新手順、誤差の復旧を定めます。JOINが多いというだけで現在情報を複写せず、代表データで測定します。
やり直すときは、実際のDB・定義・行を確認し、必要なデータを保存して、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義とデータを削除し、通常のROLLBACKでは戻せません。
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;
