本記事について
本記事では、データベースで履歴情報を管理する上で注意すべきポイント2点をまとめました。
特にクエリを覚えたての中級者が陥りがちな罠について触れているので、これから規模の大きいアプリケーションを開発される方はぜひ読んでいただけたらと思います。
導入：履歴管理について
ある程度の規模のアプリケーションを作ることになると必ず出てくるのが「履歴の管理」です。
（例：入退室履歴、商品の注文履歴など）
現在の状態のみを記録し扱うのに比べ、過去の状態も記録し扱うのも必要となると、途端に処理が複雑になります。
WHERE句やJOIN句が増えることは容易に想像つくでしょう。
なお、履歴には大きく分けて以下の2種類があり、それぞれの特徴を押さえた上でテーブルやリレーションを設計するのが大事です。
ログ
アクセスログなど、記録を残すことが主な役割であり、登録頻度は高めです。
バグ調査やデータ分析などでも時々お世話になるデータですね。
アプリケーションから参照される場合もありますが、そのままリスト表示など最低限のものに留まることが多いです。
ログテーブルの例（アクセスログテーブル）
| ID | IPアドレス | URL | デバイス | アクセス日時 |
|---|---|---|---|---|
| 1 | 172.17.1.2 | / | iOS | 2023/11/18 16:45 |
| 2 | 172.17.1.3 | /users/ | Android | 2023/11/18 16:48 |
バージョン（リビジョン）
記事の変更履歴など、データが変更されるたびに新しいバージョンまたはリビジョン番号を振って新しいレコードに書き込むタイプです。
WordPressの投稿記事テーブル（wp_posts）もこの構造になっています。
登録頻度はログに比べて低めですが、参照頻度が高めです。
基本的には新しいレコードが参照されますが、過去の状態を参照するための履歴機能が付く場合もあります。
バージョンテーブルの例（記事テーブル）
| ID | 投稿ID | リビジョン番号 | タイトル | 本文 |
|---|---|---|---|---|
| 1 | 1 | 1 | 忘年会のお知らせ(12/15) | 今年の忘年会についてお知らせします... |
| 2 | 1 | 2 | 忘年会のお知らせ(12/15→22に変更) | 【11/29追記】忘年会の日程が変更となりました... |
本題：履歴管理で注意すべきポイント2点
ポイント1. 過度な正規化に気をつける！
データベース設計について一通り学習すると、「とにかく全て第三正規形で作ればいいんでしょ」となりがちです。
原則としてはそうなのですが、履歴管理においては敢えて正規形を崩したほうがデータを扱いやすくなる場合があります。
例えばECサイトの注文履歴について考えてみましょう。
以下のようなテーブルがあったとします。
これらは全て第三正規化まで完了しています。
ユーザーテーブル
| ID | 氏名 | 配送先 |
|---|---|---|
| 1 | 三崎 翔太 | 渋谷区 |
商品テーブル
| ID | 商品名 | 単価 |
|---|---|---|
| 1001 | 青汁36本セット | 3,600 |
| 1002 | [書籍]青汁大百科 | 1,800 |
ユーザー注文履歴ヘッダテーブル
| ID | ユーザーID | 購入日時 |
|---|---|---|
| 2001 | 1 | 2023/8/10 11:45 |
ユーザー注文履歴明細テーブル
| ID | ヘッダID | 商品ID | 個数 |
|---|---|---|---|
| 3001 | 2001 | 1001 | 2 |
| 3002 | 2001 | 1002 | 1 |
ここで注文履歴ID:2001の商品金額を見たい場合は、以下のようなクエリを流すと思います。
SELECT
`a`.`商品ID`,
`b`.`商品名`,
`b`.`単価`,
`a`.`個数`,
(`b`.`単価` * `a`.`個数`) AS `金額`
FROM
`ユーザー注文履歴明細テーブル` AS `a`
INNER JOIN `商品テーブル` `b` ON `b`.`ID` = `a`.`商品ID`
WHERE
`a`.`ヘッダID` = 2001;
結果は以下の通りとなります。
| 商品ID | 商品名 | 単価 | 個数 | 金額 |
|---|---|---|---|---|
| 1001 | 青汁36本セット | 3,600 | 2 | 7,200 |
| 1002 | [書籍]青汁大百科 | 1,800 | 1 | 1,800 |
ここまでは特に問題なさそうですね。
さて、この度青汁36本セット(商品ID:1001)が期間限定キャンペーンを始め、単価が若干お安くなりました。
目立たせるために若干タイトルも変更しました。
| ID | 商品名 | 単価 | 最終更新日時 |
|---|---|---|---|
| 1001 | 【期間限定価格】青汁36本セット | 2,800 | 2023/8/20 0:00 |
| 1002 | [書籍]青汁大百科 | 1,800 | 2023/7/10 10:00 |
この状態の時、注文履歴を見るために先ほどのクエリを流すと、結果はどうなるでしょうか？
| 商品ID | 商品名 | 単価 | 個数 | 金額 |
|---|---|---|---|---|
| 1001 | 【期間限定価格】青汁36本セット | 2,800 | 2 | 5,600 |
| 1002 | [書籍]青汁大百科 | 1,800 | 1 | 1,800 |
過去の注文なのに商品名と金額が変わってしまっています。
これは大問題ですね。
本来ならば注文確定時点で商品名と金額（正確には単価）は固定されるべきで、商品データの変更によって変更されるべきではありません。
このような場合に「非正規化」が効果を発揮します。
つまり、ユーザ注文履歴明細テーブルに注文時点での商品名や単価の情報を持たせておくのです！
| ID | ヘッダID | 商品ID | 商品名 | 単価 | 個数 | 金額 |
|---|---|---|---|---|---|---|
| 3001 | 2001 | 1001 | 青汁36本セット | 3,600 | 2 | 7,200 |
| 3002 | 2001 | 1002 | [書籍]青汁大百科 | 1,800 | 1 | 1,800 |
こうすれば商品データの変更によって注文履歴の金額も変わってしまうことはありません！
また、注文金額の取得クエリもよりシンプルになり、表示速度の改善にもなります。
SELECT
`商品ID`,
`商品名`,
`単価`,
`個数`,
`金額`
FROM
`ユーザー注文履歴明細テーブル`
WHERE
`ヘッダID` = 2001;
欠点としてはデータがやや冗長になるため、ディスク容量が若干増えることです。
しかし安価で大容量のストレージがすぐに買える時代なので、処理速度低下によるCPU時間の増加やUX悪化による機会損失などによるコストと比較すれば、そこまで大きな問題にはならないでしょう。
ポイント2. 全ての履歴管理を1つのテーブルで頑張りすぎない！
実は上記の問題は商品テーブルにバージョン情報を持たせることでも解決できます。
「どうしても正規化したい！」「やっぱりデータ量が多くなると問題なのでどうにかしたい」場合はこちらの方が良いパターンもあります。
例えば先ほどの商品テーブルを以下のように変更してみましょう。
商品履歴テーブル
| 商品履歴ID | 商品ID | バージョン | 商品名 | 単価 |
|---|---|---|---|---|
| 5001 | 1001 | 1 | 青汁36本セット | 3,600 |
| 5002 | 1001 | 2 | 【期間限定価格】青汁36本セット | 2,800 |
| 5003 | 1002 | 1 | [書籍]青汁大百科 | 1,800 |
過去の商品履歴も変更後の状態も全て1つのテーブルに持たせています。
この状態だと各商品レコードは訂正以外で後から変更されることは基本的にないので、ユーザ注文履歴明細テーブルが正規化されたままでも特に問題はなさそうです。
| ID | ヘッダID | 商品履歴ID | 個数 |
|---|---|---|---|
| 3001 | 2001 | 5001 | 2 |
| 3002 | 2001 | 5003 | 1 |
その代わり、商品一覧取得クエリが以下のように少し複雑となります。
（各商品ID内で最新バージョンのレコードを1件ずつ取得する必要があるため。WINDOW関数を使ってもう少し短く書く方法もあります）
SELECT
`a`.`商品ID` AS `商品ID`,
`a`.`商品名`,
`a`.`単価`
FROM
`商品履歴テーブル` AS `a`
INNER JOIN (
SELECT
`商品ID`,
MAX(`バージョン`) AS `バージョン`
FROM
`商品テーブル`
GROUP BY
`商品ID`
) AS `b`
ON `b`.`商品ID` = `a`.`商品ID`
AND `b`.`バージョン` = `a`.`バージョン`;
「でもこれだけであれば運用上は特に問題なさそうじゃね？クエリも1回作っちゃえば終わりだし」
と思うかもしれませんが、問題はここからです。
各商品に対するレビュー機能を付けることになったとします。
その場合、以下のようなテーブルを作成することが考えられます。
商品レビューテーブル
| ID | 商品履歴ID | ユーザID | レート | コメント | 投稿日 |
|---|---|---|---|---|---|
| 6001 | 5001 | 1 | 2 | まずかったです | 2023/8/9 12:45 |
ただこのようにした場合、対象商品のバージョンが新しくなる度に商品履歴IDを変更しなければなりません。
商品レビューテーブル1個だけならまだ良いかもしれませんが、今後商品サイズテーブルや商品画像テーブルなど子テーブルが増えていくたびに更新クエリが増えることになり、きちんと開発メンバーに周知していかないと更新クエリ漏れも起こりかねません。
※「商品履歴IDではなく商品IDを外部キーとして設定すればいいんじゃね？」と思った方もいると思いますが、商品履歴テーブル内でユニークとなっていないため、商品ID内のレコードを1件削除しようとするだけでも外部キー制約違反となり、すごく扱いにくいため推奨しません。
ではこの問題に対処するにはどうすれば良いのでしょうか？
解決策はいくつかありそうですが、今回は2件紹介します。
解決策1. 最新の状態と過去の状態とを別テーブルで管理する
1つ目は、商品データを現在の状態と過去の状態とに分け、別テーブルで持たせる方法です。
商品テーブル
| 商品ID | 商品名 | 単価 |
|---|---|---|
| 1001 | 【期間限定価格】青汁36本セット | 2,800 |
| 1002 | [書籍]青汁大百科 | 1,800 |
商品履歴テーブル
| 履歴ID | 商品ID | 商品名 | 単価 | 登録日時 |
|---|---|---|---|---|
| 5001 | 1001 | 青汁36本セット | 3,600 | 2023/8/19 21:45 |
こうすれば商品IDは固定なので子テーブル追加に伴う更新クエリの増加は発生せず、商品一覧の取得も商品テーブルだけを参照すれば良いのでシンプルになります。
2テーブルの対応カラムの属性を揃えなければならない問題と商品データ更新時のテーブル数が2つに増える問題が新たに発生しますが、子テーブルのようにいくつも増えていくわけではないため保守は比較的楽になるのではと思います。
解決策2. IDだけを持つテーブルを作成する
これは※の部分で少しだけ取り上げた「商品IDを外部キーとして設定する」方法です。
具体的には、商品IDだけを持つテーブルを作成し、商品履歴テーブルやその他子テーブルの外部キーの参照先を全て商品IDテーブルに向けるようにします。
商品IDテーブル
| 商品ID |
|---|
| 1001 |
| 1002 |
商品履歴テーブル
| 履歴ID | 商品ID | バージョン | 商品名 | 単価 |
|---|---|---|---|---|
| 5001 | 1001 | 1 | 青汁36本セット | 3,600 |
| 5002 | 1001 | 2 | 【期間限定価格】青汁36本セット | 2,800 |
| 5003 | 1002 | 1 | [書籍]青汁大百科 | 1,800 |
商品レビューテーブル
| ID | 商品ID | ユーザID | レート | コメント | 投稿日 |
|---|---|---|---|---|---|
| 6001 | 1001 | 1 | 2 | まずかったです | 2023/8/9 12:45 |
商品IDテーブルにもレコードを登録する手間は増えますが、対応カラムの属性を揃える必要はないため、対応漏れによる障害が発生する可能性は低くなります。
（商品一覧取得クエリがやや複雑な状態は変わりませんが）
まとめ：履歴管理で注意すべきポイント2点
1. 正規化が基本だが、時には非正規化も考えよう
ログや注文履歴のような一度作成したら変更しないデータに関しては、作成時点の情報をコピーして登録した方が良い場合があります。
時には冗長化を恐れず設計しましょう。
2. 複数テーブルに分けてみよう
1つのテーブルで全て管理しようとすると、各種クエリが複雑になったり、機能追加でバグが起こりやすくなったりします。
解決策はいくつかありますが、現在と過去の状態とを別テーブルにしたり、扱う対象のIDだけを別テーブルに保存するなど、テーブルの分離も検討しましょう。