こんにちは。エンジニアリングマネージャーの細川です。
この記事はFLINTERSブログ祭りの一環であり、テーマは #データベース #RDB #設計 です。
RDBのテーブル設計ではデータ重複を予防するために第3正規形まで正規化するのが定石です。しかし何らかの理由があって正規化を崩すケースもあります。
最近テーブル設計について同僚と議論する機会があったので、復習も兼ねて整理してみました。
非正規化するケース
非正規化するケースは主に3つ挙げられます。
- パフォーマンス向上
- 過去データの保持による整合性確保
- 外部キー制約による整合性担保
順に見ていきましょう。
パフォーマンス向上
結合
データの結合(JOIN)は計算量が比較的大きい処理です。
アルゴリズムやインデックスの有無によりますが、M件とN件のデータを結合するには最悪でO(M*N)かかります。
結合がパフォーマンスのボトルネックになっている場合、結合不要なテーブルへと設計変更する解決策が考えられます。
結合不要にするテーブル設計変更のパターンを3つ紹介します。
ただ結論から言うと、メリットよりデメリットが大きくなりがちなため、まずはインデックスなど他の対策によりパフォーマンス向上できないか検討すべきと考えます。
インデックスを適切に使えばO(M*logN)、O(M+N)、O(M)+O(N)など遥かにマシな計算量に抑えられます。
例1：1対1対応
ある会社の従業員情報を管理する2テーブルを考えてみましょう。
[変更前]
従業員テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
従業員機密情報テーブル
- 従業員ID (主キーかつ外部キー)
- マイナンバー
- 銀行口座番号
2つのテーブルは1対1の関係です。これらのテーブルを結合不要にするため1つにまとめてみましょう。
[変更後]
従業員機密情報テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
- マイナンバー
- 銀行口座番号
- 給与
最初の例でいきなりタイトルから脱線しますが、変更前と変更後いずれも第3正規形に該当するため正規化は崩れていないです。
この設計変更のメリットは結合不要なことと、テーブル数が減るためバックアップやリストアなど保守作業が簡便になることです。
一方デメリットは、1テーブルにデータが集中することでロックの競合が起きたり、行指向DBで不要な読み込みが発生したりと別のスケーラビリティ問題が起きる可能性があることです。
また一般情報と機密情報をまとめて格納していることでアクセス権限の設定粒度が粗くなり、セキュリティ管理が難しくなる問題もあります。
例2：1対N対応
従業員が取り組む課題(issue)というデータモデルを導入してみましょう。
[変更前]
従業員テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID（外部キー）
- 完了条件
- ステータス
従業員1人は複数の課題を担当するので、2つのテーブルは1対Nの親子関係です。
これらのテーブルは第3正規形です。
課題への処理が頻繁に行われる場合、親である従業員テーブルの非キー項目（今回は氏名とメールアドレス）を子である課題テーブルに持たせて結合不要にする設計変更が考えられます。
[変更後]
従業員テーブル (変更なし)
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID (外部キー)
- 氏名
- メールアドレス
- 完了条件
- 作業記録
- ステータス
これは第1、第2正規形を満たしますが、第3正規形の推移的関数従属ルール「主キー以外の属性(氏名とメールアドレス)が他の非キー属性(担当者従業員ID)によって決定されてはならない」は満たさないので、第2正規形です。
このテーブル設計のメリットは結合が不要なことですが、
デメリットとしてはデータの重複があるほか、更新時の片テーブル反映漏れによるデータ不整合発生リスクが発生します。氏名やメールアドレスを更新するとき変更漏れに注意が必要です。
例3：N対N対応
今度は1つの課題を複数の従業員で担当できるようにする要件が発生したとします。
[変更前]
従業員テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID (外部キー)
- 完了条件
- ステータス
ここでテーブルの数を増やさないために、課題テーブルの担当者従業員IDをカンマ区切りで連結して格納できるように定義を変更してみました。
[変更後]
従業員テーブル（変更なし）
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID →例['3','5','32', ...] 外部キー制約なし
- 完了条件
- ステータス
この課題テーブルは1行の中に繰返しが存在するため非正規形です。
この設計はSQLアンチパターンのいわゆる信号無視パターンに該当し、文字列型の上限値あふれ、正規表現の実装ミス、異常値の混入など、バグ混入リスクがかなり高まるため極力避けるべきです。
このケースの適切な設計は担当テーブルを間に導入して1対N、N対1の関係に分解し、第3正規形を維持することです。
[改善案]
従業員テーブル (変更なし)
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
課題テーブル
- 課題ID (主キー)
担当者従業員ID（外部キー）削除- 完了条件
- 背景
- ステータス
担当テーブル
- 課題ID (外部キー)
- 担当者従業員ID (外部キー)
(主キーは課題IDと担当者従業員IDのペア)
設計改善前の非正規系はリスクが高いので普通やりませんが、それでもパフォーマンスのために採用する事はありえます。
別システムからカンマ区切りのデータが送られてきてそのまま格納するときや、リスト内の各要素への個別アクセスが必要ないときなど他の前提条件があってリスクを小さく抑えられる場合は検討に値するでしょう。
導出項目の保持、サマリーテーブル作成
注文管理システムのデータベースを考えてみます。
注文の合計金額など他の項目から導出できる項目はふつう第1正規化により削除しますが、導出したデータをデータ項目として保持しておく場合もあります。
商品テーブル
- 商品ID (主キー)
- 商品名
- 単価
- カテゴリID (外部キー)
注文テーブル
- 注文ID (主キー)
- ユーザーID (外部キー)
- 受注日時
注文明細テーブル
- 注文ID (外部キー)
- 明細ID
- 商品ID
- 個数
(主キーは注文IDと明細IDのペア)
注文管理システムが受注データを帳票出力するとき、合計金額を表示するためには商品テーブルと結合して個数*単価金額で都度導出しますが、
注文明細テーブルに 「金額」 という項目を追加し、計算結果をあらかじめ格納しておく設計もあります。
このような導出項目の保持パターンの応用系で、データ集計時にサマリーテーブル（マテリアライズドビュー）を作成しておくことも表示パフォーマンス確保にしばしば役に立ちます。
JOIN以外では計算量O(N)やO(N * logN)のGROUP BY、計算量O(N * logN)の ORDER BY 、正規表現なども重い処理なので、集計結果へのアクセス頻度が多い場合はデータ鮮度を犠牲にしてサマリーテーブルを作成することを検討すると良いでしょう。
過去データの保持による整合性確保
ここまでパフォーマンス観点を見てきましたが、逆に整合性を保つために非正規化する場合もあります。
たとえば上の注文管理システムで商品単価が日によって変化する前提がある場合、注文明細には当時の単価情報を保持しなければなりません。
必要なのは売ったときの単価であり、最新の単価ではないからです。
この場合の設計例は商品名と単価を注文明細テーブルにも保持することです。第1正規形になります。
[設計案1]
商品テーブル
- 商品ID (主キー)
- 商品名
- 単価
- カテゴリID (外部キー)
注文テーブルは変更なしのため省略
注文明細テーブル
- 注文ID (外部キー)
- 明細ID
- 商品ID
- 個数
- 商品名
- 単価
対案として第3正規形を維持するテーブル設計もあります。
データの有効期間を記録するValid-Timeデータモデルというパターンです。
[設計案2]
商品テーブル
- 商品ID (主キー)
- 商品名
- 単価
- カテゴリID (外部キー)
- 適用開始日時
- 適用終了日時
これのデータモデルはクエリするときの条件指定が少し増えるものの、商品名や単価などを変更しても他テーブルへの影響を最小限に抑えられる良い設計とされます。
このように「ある時点ではどうか、いつ変更したか」等の履歴を記録する設計パターンは大まかに4つあり、Snapshotモデル、Valid Timeモデル、Transactionalモデル、Bi Temporalモデルに分類されます。
奥深いので興味があれば調べてみてください。それぞれメリットとデメリットがあります。
外部キー制約による整合性担保
最後は整合性を保つためにあえて非正規化を行うケースです。ちょっと意外に思うかもしれませんが例を見てみましょう。
従業員と部署と課題の関係を考えます。
[変更前]
従業員テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
部署テーブル
- 部署ID (主キー)
- 部署名
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID (外部キー)
- 完了条件
- 背景
- ステータス
ある課題がどの部に属しているかは、３つのテーブルを結合することで検索できます。
しかし従業員が別の部署に異動した場合、異動先の部署が課題の担当部署になってしまいます。
この問題を防ぐために課題テーブルにも部署IDを保持し外部キー制約をつけます。
[変更後]
従業員テーブル
- 従業員ID (主キー)
- 氏名
- メールアドレス
- 所属部署ID
- 職位
部署テーブル
- 部署ID (主キー)
- 部署名
課題テーブル
- 課題ID (主キー)
- 担当者従業員ID (外部キー)
- 完了条件
- 背景
- ステータス
- 部署ID (外部キー)
これは課題テーブルの部署IDが担当者従業員IDを経由して推移的関数従属するため、第2正規形となります。
しかしこの設計のほうが整合性を担保できて使いやすそうです。
まとめ
非正規化を崩すケースについてパフォーマンス観点と整合性観点から色々見てきました。
長くて読むのが大変な記事になってしまいましたが、最適なテーブル設計をする参考になれば幸いです。
参考文献
- 三好康之(2018.09.20)情報処理教科書 データベーススペシャリスト 2019年版 第4章10節
- Bill Karwin(2016.12.13)SQLアンチパターン p3-11
