目次
はじめに
そもそも正規化とは何か
正規化で出てくる「エンティティ」とは何か
エンティティの「同一性」とは何か
正規化の段階
正規化はどこまでやるべきか
第3正規形までを基本と考える理由
ただし、要件によっては正規化しすぎない判断も必要
設計段階で、どれくらいパフォーマンスが出るかはどう考えるのか
正規化だけでなく、全体設計で考える必要がある
まとめ
はじめに
今回は、「データベースの正規化はどこまでやるべきか？」という疑問について、調べたことをまとめます。
この記事を書くきっかけとなったのは、実際に開発に携わっているシステムで、パフォーマンス改善を求められ、
調べていく中で、パフォーマンスにはSQLやインフラだけでなく、そもそものDB設計が大きく影響することを知り、改めて学んでみたいと思ったからです。
今回の結論を先に書くと、次のとおりです。
- データベースの正規化は、原則として第3正規形までを基本とする
- ただし、システム要件によっては柔軟に対応する必要がある
この記事では、その理由を、正規化の基本から順番に整理していきます。
そもそも正規化とは何か
今回取り扱うのは、リレーショナルデータベースです。
これは、私たちが普段よく目にするような、表形式でデータを管理するデータベースです。
では、正規化とは何かというと、データを重複しすぎないように整理し、更新しやすく、矛盾が起きにくい形にすることです。
たとえば、ネットショップの注文管理を考えてみます。
もし1つの表の中に、お客さんの名前、住所、注文した商品、単価、仕入先といった情報をすべて入れていたとします。
このとき、たとえば田中さんが引っ越して住所を変更した場合、過去を含む複数の注文データに同じ住所が入っていれば、それらをすべて更新しなければなりません。
もし更新漏れがあれば、同じ田中さんなのに古い住所と新しい住所が混在してしまいます。
これは、データの整合性が崩れた状態です。
そこで、顧客情報を注文情報から切り分けて、顧客は顧客テーブル、注文は注文テーブルで管理し、注文側には顧客IDだけを持たせるようにします。
すると、住所変更があっても顧客テーブルの1行だけ直せば済みます。
このように、重複を減らし、更新の責任範囲を明確にするために表を整理する考え方が正規化です。
正規化で出てくる「エンティティ」とは何か
DBの正規化を考えるときには、エンティティという考え方が重要になります。
ここでは、エンティティとは業務の中で、ひとつのものとして扱いたい対象と考えるとわかりやすいです。
たとえば、注文管理のシステムであれば、次のようなものがエンティティになります。
- 顧客
- 注文
- 商品
- 仕入先
- 担当者
正規化とエンティティが関係するのは、「その情報は、本来どの対象のものか」を整理していくことが、正規化そのものだからです。
たとえば、1つの表の中に、顧客名、顧客住所、商品名、仕入先名、注文日などをすべて持たせてしまうと、顧客の情報、商品の情報、仕入先の情報、注文の情報が1か所に混ざることになります。
このような状態では、たとえば顧客の住所変更があったときに、複数の注文データをすべて更新しなければならず、更新漏れが起きると整合性が崩れてしまいます。
そこで正規化では、顧客に属する情報は顧客へ、商品に属する情報は商品へ、注文に属する情報は注文へというように、情報の置き場所を整理していきます。
つまり、正規化とは単に表を細かく分けることではなく、それぞれの情報を、本来属するエンティティに戻していくこととも言えます。
そのため、正規化を考えるときには、先に「このシステムでは、何を独立した実体として扱うのか」を整理することが大切になります。
どの情報がどのエンティティに属するのかが見えると、どこでデータが重複しているのか、どこで更新時の矛盾が起きやすいのかも見えやすくなります。
エンティティの「同一性」とは何か
エンティティの話では、同一性という考え方も重要です。
これは簡単にいうと、「これが前と同じものだと、ぶれずに識別できること」です。
たとえば顧客であれば、名前や住所、電話番号が変わることがあります。
それでも、顧客IDが同じであれば同じ顧客であると判断できるようにしておく必要があります。
もし注文テーブルの中に顧客名や住所を毎回ベタ書きしていたら、表記ゆれや更新漏れによって、「これは同じ取引先なのか？」が曖昧になっていきます。
だからこそ、正規化では同じ実体の情報を1か所に集約し、他のテーブルからはIDで参照する形が重要になります。
これは単に表をきれいにするというより、「同じものを同じものとして扱い続けるための設計」とも言えます。
正規化の段階
正規化には段階があり、代表的なものとして次の3つがあります。
- 第1正規形
- 第2正規形
- 第3正規形
- ボイス・コッド正規形
- 第4正規形
- 第5正規形
細かい定義を厳密に説明し始めると長くなるため、ここでは概要だけ整理します。
第1正規形は、1つのセルには1つの値だけを持つようにする形です。
たとえば、商品欄に「りんご、みかん」のように複数の値を入れず、表として扱いやすく整理します。
第2正規形は、主キーの一部にしか依存していない項目を分離し、そのデータが本来属する先に移す考え方です。
第3正規形は、主キーに直接関係せず、他の非キー項目に依存している項目を分離し、そのエンティティに本当に必要な属性だけを持たせる形に近づけます。
実務では厳密な定義を丸暗記するよりも、この項目は本当にこのテーブルに属するのか、同じ情報を何度も持っていないか、更新時に矛盾が起きないかという視点で考える方が理解しやすいかもしれません。
正規化はどこまでやるべきか
ここで最初の問いに戻ります。
正規化とは整理することなので、最初は「できるだけ細かく分けた方がいいのでは？」と思うかもしれません。
ただ、実際にはそう単純ではありません。
正規化を進めると、たしかに重複データが減り、更新漏れが起きにくくなり、データの整合性が保ちやすくなります。
一方で、表を分ければ分けるほど、データを取り出すときには複数テーブルを結合する必要が出てきます。
たとえば、「田中さんが買った商品の仕入先を知りたい」という問い合わせをしようとすると、顧客テーブル、注文テーブル、注文明細テーブル、商品テーブル、仕入先テーブルのように、複数の表を結合することになります。
そのため、正規化を進めれば進めるほど整合性は高まりやすい一方で、検索や一覧表示のパフォーマンスに影響する可能性があるという面もあります。
つまり、正規化は「細かく分ければ分けるほど正しい」というものではなく、整合性と性能のバランスで考える必要があるということです。
第3正規形までを基本と考える理由
では、なぜ「原則として第3正規形まで」と言われることが多いのでしょうか。
それは、第3正規形あたりまで整理すると、次のような実務上の大きなメリットが得られるからです。
- データの重複をかなり減らせる
- 更新時の矛盾を防ぎやすい
- 設計意図が比較的わかりやすい
一方で、それ以上の正規化を強く意識しすぎると、設計や利用の複雑さが増しやすく、すべてのシステムで必ずしも効果が見合うとは限りません。
そのため、まずは「業務上のエンティティを整理し、同一性を明確にし、第3正規形までを基本として設計する」という考え方が、実務上の出発点として妥当だと考えられます。
ただし、要件によっては正規化しすぎない判断も必要
ここで大事なのは、第3正規形が絶対正義ではないということです。
システムによっては、読み取り性能を優先して、一部の情報をあえて冗長に持つことがあります。
いわゆる非正規化です。
たとえば、非常に件数の多い検索画面や一覧画面で、顧客名、商品名、仕入先名、担当者名などを毎回多段結合して取得すると、レスポンスが悪化することがあります。
そのような場合には、次のような工夫が検討されます。
- 表示用のビューを作る
- 検索用の集約テーブルを持つ
- 一部の名称を冗長に保持する
- キャッシュやインデックス設計で補う
ただし、このときも「なんとなく速そうだから冗長に持つ」のではなく、どの画面が遅いのか、どの検索条件が重いのか、更新頻度と参照頻度のどちらが高いのかを見たうえで判断する必要があります。
つまり、非正規化は正規化を知らないまま崩すものではなく、正規化したうえで目的を持って崩すものだと理解しました。
設計段階で、どれくらいパフォーマンスが出るかはどう考えるのか
ここで疑問になるのが、「でも設計段階では、まだ実データも少ないし、本当に速いかどうか分からないのでは？」という点です。
これはその通りで、設計段階で実運用と完全に同じ性能を断言することはできません。
ただし、ある程度見積もることはできます。
見るポイントは主に次のようなものです。
- どの処理が多いかを整理する
- 想定件数を置く
- 想定SQLを書いてみる
- 試作して実行計画を見る
1. どの処理が多いかを整理する
まず大事なのは、このシステムで何が一番多く使われるのかを把握することです。
一覧検索が中心なのか、登録・更新が多いのか、バッチ処理が重いのか、明細件数が膨らむのかによって、重視すべき設計が変わります。
2. 想定件数を置く
1日あたりの登録件数、1年後のレコード件数、1件の親に対する子の件数、1回の検索で絞り込まれる件数などを、ざっくりでも想定します。
完全に正確な数字でなくても、桁感を持つだけでも設計判断はかなり変わると考えられます。
3. 想定SQLを書いてみる
ER図だけ見ていても、性能は見えにくいです。
そのため、一覧画面の検索SQL、詳細画面の取得SQL、集計処理のSQL、更新時のSQLなど、実際にどういうSQLになるかを早めに書いてみることが有効です。
この時点で、結合が多すぎないか、絞り込み条件にインデックスが効きそうか、ソートや集計が重くなりそうかが見えてきます。
4. 試作して実行計画を見る
できれば、設計段階でも簡単な試作環境を作って、想定テーブル、想定件数に近いダミーデータ、想定SQLを用意し、実行計画を確認するのが有効です。
これにより、だいたいの感覚がつかめます。
つまり、設計段階での性能見積もりは、頭の中だけで判断するのではなく、想定SQLとダミーデータで早めに試すことが大切です。
正規化だけでなく、全体設計で考える必要がある
今回調べて感じたのは、DB設計は単純に「正規化すればよい」というだけではないということです。
実際には、次のようなさまざまな要素が関わります。
- テーブル設計
- 主キー・外部キー設計
- インデックス設計
- SQLの書き方
- 画面で一度に表示する件数
- バッチ処理との役割分担
そのため、正規化の議論をするときも、「どこまで分けるか」だけでなく、「このシステムで何を優先したいのか」まで含めて考える必要があるのだと思いました。
まとめ
今回調べた内容をまとめると、次のようになります。
データベースの正規化とは、データの重複や矛盾が起きにくいように表を整理することです。
その前提として、顧客・注文・商品といった業務上の実体であるエンティティを整理し、同一性をIDで安定して扱えるようにすることが重要です。
そのうえで、正規化を進めることで整合性は高まりやすくなりますが、表を分けすぎると検索時の結合が増え、パフォーマンスに影響することがあります。
だからこそ、原則としては第3正規形までを基本としつつ、システム要件に応じて柔軟に判断するという考え方が現実的だと感じました。
また、設計段階で性能を考えるときは、テーブル定義だけを見るのではなく、どの処理が多いか、どのくらいの件数になるか、どんなSQLになるか、どんなインデックスが必要か、ダミーデータで実行計画を確認できるかといった観点で、早い段階から検証していくことが大切です。
正規化は、単に表を細かく分けるためのルールではなく、データを矛盾なく扱い、変更に強いシステムを作るための考え方なのだと理解できました。
今後もDBについての理解を深めていきたいと思います。
参考文献
- 『達人に学ぶDB設計徹底指南書』翔泳社
