履歴table

参考

まあまあ

もう少し整理できそう

いくつの日付を持つか

start_dateだけ持つ

start_dateとend_dateの両方をもつ

履歴のためのテーブルを別途用意するかどうか

同じテーブルに履歴を追加していくか

書き込みが1回で済む

selectする際に、最新の行を特定する必要がある

似た構造の別のテーブルを用意するか

2つのテーブルに同時に買い込む必要がある

別のテーブルを用意する場合、複数のtableでデータを重複させるかどうか

重複させない

重複させる

あるいは、materialized viewなどを使うか

他のテーブルからの参照の仕方

常に最新だけを見れれば良い

作成した参照を参照し続けたい

e.g. 注文した時点での価格を参照し続ける

code:gpt

DB tableで履歴を管理するパターンの整理がしたいです。

下記のメモをstep-by-stepで整理して、足りない言葉を文脈から補完し，わかりやすく具体的な言葉で説明した後、要件を満たすように清書してください

- 商品の価格の履歴を管理する例としたい

- 最低限必要なpropertyは、product_id, name, price, start_date

- メリット・デメリットを列挙してください

- 以下のようなユースケースでのSQL例を列挙してください

- create

- 最新のpriceだけを対象にproductをread

- 過去のpriceも含めてproductをread

- update

- delete

- ER図をMemaidのerDiagramで書いてください

- 図の中にはproductを参照するOrder tableも書いてください

- 文章内の例の値が間違っている場合は修正してください

- メモの記述が論理的に誤っている場合は修正してください

最新と過去のものとでtableを分けるパターン

producには最新のデータを、historyの方には過去の価格を保存する

table:product

product_id name price start_date

1 A 10000 2010-01-01

2 B 5000 2014-04-01

3 C 20000 2012-01-01

table:price_history

product_id price start_date

2 4000 2013-04-01

3 18000 2010-01-01

各テーブルにデータの重複はない

最新のものを取得するのは簡単

過去のものも含めて調べる場合はちょっと面倒だがまだまし

UNIONなどで、両tableをmergeする

e.g. 過去から現在までに1万円以上の価格付がされたことのある商品を探す

別のEntityから最新のproductを参照する際は、product_idに外部キー制約を作れば良い

過去の履歴も含めた概念に対しては外部キー制約を作ることができない

2つのtableに跨っているため。

作成した参照を参照し続けるには工夫が必要

データの不整合が起きうる

両tableに同じ行が含まれることを制限できない

Create

productにinsertするだけ

Read

最新の価格を取得するqueryはかなりシンプル

code:sql

select price

from product

where name = 'A'

Update

productでの更新と、hitoryへの追加

code:sql

BEGIN;

INSERT INTO price_history(product_id, price, start_date)

SELECT product_id, price, start_date

FROM product

WHERE name = 'A';

UPDATE product

SET price = 11000, start_date = '2023-05-19'

WHERE name = 'A';

COMMIT;

https://gyazo.com/627db920d63e3934a9831eb985883c51

最新と過去のものとでtableを分けるが、重複した行を許容するパターン

table:product

name price start_date

A 10000 2010-01-01

B 5000 2014-04-01

C 20000 2012-01-01

table:price_history

name price start_date

A 10000 2010-01-01

B 4000 2013-04-01

B 5000 2014-04-01

C 18000 2010-01-01

C 20000 2012-01-01

重複がある状態が正解、というようにする

制約が多い

これはmaterialized viewを使っている

けどちょっと違うな

end_date持ってるし

サロゲートキーを使うパターン

履歴を一意に管理するid、それを参照するproductと、historyを作る

table:price_id

price_id

1

2

3

4

5

table:product

price_id name price start_date

1 A 10000 2010-01-01

3 B 5000 2014-04-01

5 C 20000 2012-01-01

table:product_history

price_id item price start_date

2 B 4000 2013-04-01

4 C 18000 2010-01-01

JOINが増えがち

だるそう

https://gyazo.com/197ad668846e52874e70928aed416535

サロゲートキーを使うパターン2

table:product

product_id history_id

1 2

2 3

table:price_history

history_id product_id item price start_date

1 1 B 4000 2013-04-01

2 1 B 5000 2020-04-01

3 2 C 18000 2010-01-01

履歴はhistory側にのみ持たせる

productは、最新履歴への参照を持つ

相互参照になるので両方をnot nullにすることができない(?)

transactionあっってもむり？

適用する日付を持たせるパターン

適用する開始日のみを持たせるパターン

table:product_item(現在=2014/9)

name price start_date

A 10000 2010-01-01

B 4000 2013-04-01

B 5000 2014-04-01

C 18000 2010-01-01

C 20000 2012-01-01

SQLの例

以下の2つはいずれも、将来の価格データがない前提

code:sql

select price

from product_item

where item = 'A'

and order by start_date desc limit 1

code:sql

select price

from product_item

where item = 'A'

and start_date = (

select max(start_date)

from product_item

where item = 'A'

)

ここでは悪い例として挙げられている

行によって意味が異なる

現在の有効な価格と、過去の無効な価格

有効なものの一覧を出すのが面倒そう

window関数が必要になりそう

このテーブルを参照している別のtableがある場合に、更新のたびに参照を切り替えないといけない

履歴を参照するテーブルの過去の情報が変更されるパターン

履歴を参照するテーブルの過去の情報が変更されないパターン

過去の事実(値)が失われる

商品価格が変わるとorderの合計値も変わっちゃうやつ

過去の事実(過程)が失われる

statusを上書きすると、どういう経過をたどってそのstatusになったのかの意味が変わる

e.g.

発注済み→キャンセル→再発注→配送済み

発注済み→配送済み

↑結果は同じだが、たどっている過程が異なる。これを同一視してしまう

もっかい読もう

回避策

orderに消費税率も持たせる

statusのみを積んでいくtableを作る

他の回避策