なぜDBを正規化or非正規化するのか
業務でテーブル設計を行うことになったが、非正規化するメリットがいまいち理解できていない。
正規化・非正規化する理由、非正規化する具体例について調べたので、個人的なメモを整理しておく。
なぜ正規化するのか
-
テーブルの冗長性を排除するため。
テーブルの冗長性を排除することで更新効率を向上し、データの一貫性を担保する。
ここでの冗長とは、一つの情報が複数のテーブルに存在するようなケースを意味する。
一つの情報が複数のテーブルに存在すると、その情報を更新する際は複数のテーブルを更新しなければならない。また、更新時のタイムラグによって片方のテーブルの情報だけ更新されているような状態があり得る。 -
テーブルの柔軟性を高めるため。
新しい情報を追加する場合やモデルを変更する場合、正規化されている方が容易に拡張できる。
正規化の欠点
概要
複数のテーブルから情報を取得する場合にテーブルの結合操作（join）が必要になるため、SQLのパフォーマンスが劣化する可能性が高い。改善するには非正規化を行うか、SQLのチューニングを行う。
情報を更新する場合は非正規化されていると、パフォーマンスは劣ることがある。
正規化されているテーブルから情報を取得
正規化されているテーブルから情報を取得するとき、欲しい情報は複数のテーブルにまたがって存在することが多い。その場合、テーブルを結合して情報を取得する。
テーブルの結合操作を行うSQLはパフォーマンスを著しく劣化させてしまう。これが正規化の大きな欠点である。
正規化していなければテーブルの結合は不要なので、シンプルなSQLで情報を取得できる。
正規化されているテーブルの情報を更新
ただし情報を更新する場合、非正規化されているとパフォーマンスが劣る可能性が高い。
例えばユーザーの所属企業が変わった場合、非正規化されているテーブルだと全てのレコードの所属企業を更新する必要がある。
正規化するか非正規化するか
正規化すると検索SQLのパフォーマンスに大きな影響を与える。
テーブル設計において正規化するか、非正規化するかは重要なポイントと考える。
パフォーマンスを著しく劣化させるようなケースが想定されるなら非正規化を考え、非正規化すべき理由を整理しておくべきと考える。もちろんパフォーマンス以外のデータのリアルタイムな整合性や更新のパフォーマンスも考慮した上で。
最も不適切なのはあとで非正規化すればいいやという考え方。データモデルの変更は改修コストが大きいため。もう変えることはない気概で、未来も想定した上で設計すべき。
どのような場合に非正規化するのか
正規化、非正規化のトレードオフは整理できたが、実際に非正規化するようなケースをイメージできていないので具体例を調べてみた。
ただしあくまでも例であるため、この例と同じケースだから非正規化すべきというわけではない。先に述べた通り、検索時および更新時のパフォーマンス、データの整合性、他プロジェクトにおける諸条件を考慮した上で検討すべき。
- 検索SQLのパフォーマンス劣化を防止するケース
これまでに上述した通り、正規化されていることで検索のパフォーマンスが劣ることがある。それを予防するために非正規化する。
例えば、ある画面において複数のテーブルから大量のデータを取得しなければならない場合、アソシエーションを辿る必要があるので検索処理が重くなってしまう。一つのテーブルに情報を集約し、アソシエーションを辿る必要をなくし、処理を軽くすれば良い。 - 過去の情報を残しておくケース
例えば、商品の単価など過去の情報を残しておきたいケースがある。その時々の商品単価を残しておきたい場合は、非正規化することが一つの手。 - 現在と過去の情報を分離することで検索や更新などの処理を軽量化するケース
例えば契約情報についてのテーブルを用意する場合、将来的には過去の契約情報が大量に登録されることが想定できる。（現在の契約情報が1000件、過去の契約情報が数万件のような）
あらかじめ現在の契約情報を格納するテーブルと過去の契約を格納するテーブルを格納しておくことで、現在の契約情報を扱う処理を軽量化できる。
参考記事
達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ
もう一度学ぶMS-Access - 非正規化
Discussion