一覧DB-06-3 · 正規化と設計

履歴データと集計値の設計

商品価格を変更し、現在価格で過去注文を計算すると金額が変わることを確かめます。価格履歴を期間で検索し、期間の重なりを検出します。最後に注文の集計値を別表へ保存し、原表更新後のずれと更新手順を確認します。

説明

現在の商品価格と、注文時に合意した価格は別の事実です。過去の取引を再現するための保存と、同じ情報を読み出しやすく複製する設計では、更新の方法や整合性の確認が異なります。

1現在価格と注文時の単価

商品P1は100円、注文101は2個を100円で購入した記録にします。

CREATE TABLE products(sku TEXT PRIMARY KEY,price INTEGER NOT NULL)現在の商品価格

 STRICT;
CREATE TABLE orders(id INTEGER PRIMARY KEY,sku TEXT REFERENCES products(sku),qty INTEGER,

unit_price INTEGER注文時の合意単価

) STRICT;
INSERT INTO products VALUES('P1',100);
INSERT INTO orders VALUES(101,'P1',2,100);
SELECT * FROM orders;

unit_priceは注文時の合意単価です。products.priceの現在値を単に複製したキャッシュではなく、取引ごとに保持する事実として定義しています。数量との積は200円です。

2現在価格を120円へ変更する

現在価格と注文時単価の両方で金額を計算します。

UPDATE products SET price=120現在価格だけを改定

 WHERE sku='P1';
SELECT o.id,

p.price*o.qty現在価格で再計算 AS using_current,o.unit_price*o.qty取引時単価で計算 AS agreed_total FROM orders AS o JOIN products AS p ON p.sku=o.sku;

現在価格では240円、合意した取引金額は200円です。現在マスタだけに頼って再計算すると、過去注文の金額が変わってしまいます。名前が似た価格列でも、時点と意味が違えば保存先を分ける理由があります。

3価格の有効期間を表にする

期間を開始日を含み終了日を含まない区間として保存します。

CREATE TABLE price_history(sku TEXT,valid_from TEXT,valid_to TEXT,price INTEGER,PRIMARY KEY(sku,valid_from)商品と開始日で履歴行を識別,CHECK(valid_to IS NULL OR valid_from<valid_to)行内の期間の前後関係

) STRICT;
INSERT INTO price_history VALUES('P1','2026-11-01','2026-11-05',100),('P1','2026-11-05',NULL,120);
SELECT * FROM price_history ORDER BY valid_from;

100円は11月1日以上5日未満、120円は5日以降です。終了NULLは終期未定を表します。この例は固定幅YYYY-MM-DDの日付文字列なので、文字列の順序で日付を比較できます。CHECKは各行の開始と終了の前後関係を検査します。

4改定の前日と当日の価格

11月4日と5日の有効な価格をそれぞれ取得します。

SELECT price FROM price_history WHERE sku='P1' AND valid_from<='2026-11-04'開始日を含む AND (valid_to>'2026-11-04'終了日を含まない

 OR valid_to IS NULL);
SELECT price FROM price_history WHERE sku='P1' AND

valid_from<='2026-11-05'改定当日の開始条件 AND (valid_to>'2026-11-05' OR valid_to IS NULL);

4日は100円、5日は120円です。終了日を含めないため、5日の境界で旧価格と新価格が二重に有効になりません。期間の端を含むかどうかは、保存と検索で統一します。

5行単位のCHECK制約では期間の重複を防げない

BEGIN内で4日から6日までの価格を追加します。

BEGIN重複期間の試行を開始

;
INSERT INTO price_history

VALUES('P1','2026-11-04','2026-11-06',110)既存期間と重なる行

;
SELECT price FROM price_history WHERE sku='P1' AND valid_from<='2026-11-05' AND (valid_to>'2026-11-05' OR valid_to IS NULL) ORDER BY valid_from;

110円と120円の二行が返ります。追加行は開始より終了が後で主キーも異なるため、定義した制約には違反しません。他の行の期間との重なりは、行内のCHECKとは別の条件です。

6重なった期間の組を検出して戻す

同じ商品の異なる履歴行を結び、期間の交差を調べます。

SELECT a.valid_from AS first_start,b.valid_from AS second_start FROM price_history AS a JOIN price_history AS b ON a.sku=b.sku AND

a.valid_from<b.valid_from同じ組の重複表示を避ける WHERE (a.valid_to IS NULL OR b.valid_from<a.valid_to一方の開始が他方の終了より前

) AND (b.valid_to IS NULL OR a.valid_from<b.valid_to) ORDER BY a.valid_from,b.valid_from;

ROLLBACK試した期間追加を取り消す;

1日開始と4日開始、4日開始と5日開始の二組が重なります。検出後に追加を取り消しました。複数行にまたがる重複を保存時に防ぐには、DBの機能に応じた制約やトリガーなどが必要で、同時更新も考慮します。

7注文の集計結果を別表へ保存する

商品の受注個数と金額を集計して保存します。

CREATE TABLE order_totals(sku TEXT PRIMARY KEY,qty INTEGER,total INTEGER) STRICT;

INSERT INTO order_totals SELECT集計結果を別表へ保存 sku,SUM(qty),SUM(qty*unit_price)取引時単価で金額を集計

 FROM orders GROUP BY sku;
SELECT * FROM order_totals;

P1は2個・200円です。こちらは注文の原表から再計算できる複製です。保存済み集計を読むことで毎回の集約を省けますが、更新時には原表との同期が必要になります。

8原表だけを更新すると集計が古くなる

120円の新規注文を一件追加します。

INSERT INTO orders VALUES(102,'P1',1,120)原表へ注文を追加

;
SELECT

'stored'保存済み集計 AS source,qty,total FROM order_totals UNION ALL SELECT 'recomputed'原表からの再計算,SUM(qty),SUM(qty*unit_price) FROM orders;

保存済みは2個200円、再計算は3個320円です。表を別に作っただけでは自動更新されません。どちらを正本とし、いつどの手順で集計を更新するかを決める必要があります。

9保存済み集計を更新する

原表から再集計し、SKUがある場合は集計値を置き換えます。

INSERT INTO order_totals SELECT sku,SUM(qty),SUM(qty*unit_price) FROM orders WHERE true GROUP BY sku現在の原表を商品別に再集計 ON CONFLICT(sku) DO UPDATE既存SKUは集計値を置換

 SET qty=excluded.qty,total=excluded.total;
SELECT * FROM order_totals;

3個・320円になります。SQLiteのINSERT SELECTで後続のONを結合条件と取り違えないよう、WHERE trueを入れています。この更新は原表に残るSKUが対象なので、最後の注文を削除したSKUまで消すには別の削除処理か全件再構築が必要です。

10原表と集計を一緒に確定する

一つのトランザクションで注文追加と集計更新を行います。

BEGIN原表と集計の変更をまとめる

;
INSERT INTO orders VALUES(103,'P1',2,120);
UPDATE order_totals SET qty=qty+2,

total=total+240新規注文の金額を反映

 WHERE sku='P1';

COMMIT両方の変更を確定

;
SELECT qty,total FROM order_totals;
SELECT SUM(qty) AS qty,SUM(qty*unit_price) AS total FROM orders;

両方とも5個560円です。一緒に確定する方式でも、更新式の正しさ、削除や訂正時の処理、再試行で二重加算しない仕組みが必要です。履歴の保存と読み出し用複製を分け、後者は原表から検査・再構築できるようにします。

まとめ

現在値と取引時の値は別の事実で、必要な時点の情報を保存します。

期間の境界と重なりを検査し、行内のCHECKだけで保証できる範囲を把握します。

集計の複製には更新手順が必要で、正本との照合と再構築方法を用意します。

操作履歴と解説を振り返れます。

SQLite 3.54.0

CREATE TABLE products(sku TEXT PRIMARY KEY,price INTEGER NOT NULL) STRICT;
CREATE TABLE orders(id INTEGER PRIMARY KEY,sku TEXT REFERENCES products(sku),qty INTEGER,unit_price INTEGER) STRICT;
INSERT INTO products VALUES('P1',100);
INSERT INTO orders VALUES(101,'P1',2,100);
SELECT * FROM orders;

╭─────┬─────┬─────┬────────────╮
│ id  │ sku │ qty │ unit_price │
╞═════╪═════╪═════╪════════════╡
│ 101 │ P1  │   2 │        100 │
╰─────┴─────┴─────┴────────────╯

UPDATE products SET price=120 WHERE sku='P1';
SELECT o.id,p.price*o.qty AS using_current,o.unit_price*o.qty AS agreed_total FROM orders AS o JOIN products AS p ON p.sku=o.sku;

╭─────┬───────────────┬──────────────╮
│ id  │ using_current │ agreed_total │
╞═════╪═══════════════╪══════════════╡
│ 101 │           240 │          200 │
╰─────┴───────────────┴──────────────╯

CREATE TABLE price_history(sku TEXT,valid_from TEXT,valid_to TEXT,price INTEGER,PRIMARY KEY(sku,valid_from),CHECK(valid_to IS NULL OR valid_from<valid_to)) STRICT;
INSERT INTO price_history VALUES('P1','2026-11-01','2026-11-05',100),('P1','2026-11-05',NULL,120);
SELECT * FROM price_history ORDER BY valid_from;

╭─────┬────────────┬────────────┬───────╮
│ sku │ valid_from │  valid_to  │ price │
╞═════╪════════════╪════════════╪═══════╡
│ P1  │ 2026-11-01 │ 2026-11-05 │   100 │
│ P1  │ 2026-11-05 │ NULL       │   120 │
╰─────┴────────────┴────────────┴───────╯

SELECT price FROM price_history WHERE sku='P1' AND valid_from<='2026-11-04' AND (valid_to>'2026-11-04' OR valid_to IS NULL);
SELECT price FROM price_history WHERE sku='P1' AND valid_from<='2026-11-05' AND (valid_to>'2026-11-05' OR valid_to IS NULL);

╭───────╮
│ price │
╞═══════╡
│   100 │
╰───────╯
╭───────╮
│ price │
╞═══════╡
│   120 │
╰───────╯

BEGIN;
INSERT INTO price_history VALUES('P1','2026-11-04','2026-11-06',110);
SELECT price FROM price_history WHERE sku='P1' AND valid_from<='2026-11-05' AND (valid_to>'2026-11-05' OR valid_to IS NULL) ORDER BY valid_from;

╭───────╮
│ price │
╞═══════╡
│   110 │
│   120 │
╰───────╯

SELECT a.valid_from AS first_start,b.valid_from AS second_start FROM price_history AS a JOIN price_history AS b ON a.sku=b.sku AND a.valid_from<b.valid_from WHERE (a.valid_to IS NULL OR b.valid_from<a.valid_to) AND (b.valid_to IS NULL OR a.valid_from<b.valid_to) ORDER BY a.valid_from,b.valid_from;
ROLLBACK;

╭─────────────┬──────────────╮
│ first_start │ second_start │
╞═════════════╪══════════════╡
│ 2026-11-01  │ 2026-11-04   │
│ 2026-11-04  │ 2026-11-05   │
╰─────────────┴──────────────╯

CREATE TABLE order_totals(sku TEXT PRIMARY KEY,qty INTEGER,total INTEGER) STRICT;
INSERT INTO order_totals SELECT sku,SUM(qty),SUM(qty*unit_price) FROM orders GROUP BY sku;
SELECT * FROM order_totals;

╭─────┬─────┬───────╮
│ sku │ qty │ total │
╞═════╪═════╪═══════╡
│ P1  │   2 │   200 │
╰─────┴─────┴───────╯

INSERT INTO orders VALUES(102,'P1',1,120);
SELECT 'stored' AS source,qty,total FROM order_totals UNION ALL SELECT 'recomputed',SUM(qty),SUM(qty*unit_price) FROM orders;

╭────────────┬─────┬───────╮
│   source   │ qty │ total │
╞════════════╪═════╪═══════╡
│ stored     │   2 │   200 │
│ recomputed │   3 │   320 │
╰────────────┴─────┴───────╯

INSERT INTO order_totals SELECT sku,SUM(qty),SUM(qty*unit_price) FROM orders WHERE true GROUP BY sku ON CONFLICT(sku) DO UPDATE SET qty=excluded.qty,total=excluded.total;
SELECT * FROM order_totals;

╭─────┬─────┬───────╮
│ sku │ qty │ total │
╞═════╪═════╪═══════╡
│ P1  │   3 │   320 │
╰─────┴─────┴───────╯

BEGIN;
INSERT INTO orders VALUES(103,'P1',2,120);
UPDATE order_totals SET qty=qty+2,total=total+240 WHERE sku='P1';
COMMIT;
SELECT qty,total FROM order_totals;
SELECT SUM(qty) AS qty,SUM(qty*unit_price) AS total FROM orders;

╭─────┬───────╮
│ qty │ total │
╞═════╪═══════╡
│   5 │   560 │
╰─────┴───────╯
╭─────┬───────╮
│ qty │ total │
╞═════╪═══════╡
│   5 │   560 │
╰─────┴───────╯

演習完了