テーブルの正規化が不十分な場合に発生する更新時異状の具体的なパターンと対策｜データベース基礎

はじめに

テーブル設計時に正規化が不十分なため、

発生してしまう更新時異状のパターンとその対策について具体例でまとめました。

この記事を読む前に

本記事では正規化についての理解が前提になるので

必要であればこちら先にご覧ください

データベースの正規化の目的とは？？｜非正規形〜第３正規形の正規化のやり方

更新時異状

正規化が不十分(第１正規形、第２正規形のまま)だと、

テーブルに新しい行を挿入、更新、削除する際に異状が発生します。

更新時異状が発生する理由としてはテーブルに冗長性があることが挙げられます。

正規化の目的はこの冗長性を排除して「１事実１箇所」(1 fact in 1 place)にすることです。

それでは、第２正規化、第３正規化のままの場合に発生する更新時異状のケースのそれぞれ、

具体的な例で見ていきます。

第２正規化されていない(第１正規形のまま)場合に発生する更新時異状

まずは、具体例を見て下さい。

上の図は第２正規化されていないテーブルとその関数従属性を表したモノです。

この状態を部分関数従属という言い方をしますが、テーブルの主キーの一部に従属性がある状態です。

(店舗IDと商品IDが主キーだが店舗IDが決まるだけで店舗名が決まる状態)

テーブルの例を見ると、

{店舗ID：01、店舗名：東京店}という情報が２レコード、

{商品ID：001、商品名：釣り竿A}という情報が２レコード、

それぞれ存在していて冗長です。(「１事実１箇所」(1 fact in 1 place)ではない)

こういうデータの持ち方をしてしまうと、、、

例えば店舗ID：01の店舗名を東京店→東京新宿店に修正したい場合、

同じ修正を２レコードに対して行わなければなりません。

１箇所でも修正が漏れると整合性が失われてしまいます。(修正時の更新時異状)

また、

新しい商品{商品ID：004、商品名：リールD}を登録したい場合、

主キーが{店舗ID、商品ID}なので、

商品を登録したいだけなのに店舗IDを必ず登録する必要があり、

店舗IDをNULLのままで登録しようとすると主キー制約違反になります。(挿入時の更新時異状)

逆に{店舗ID：01、店舗名：東京店}を削除したい場合、

店舗だけを削除したいのに商品があり、

もちろん、

店舗IDをNULLで更新しようとすると主キー制約違反になります。(削除時の更新時異状)

対策としては

店舗商品在庫テーブル(店舗ID、商品ID、在庫)　主キー：店舗ID、商品ID

店舗テーブル(店舗ID、店舗名)　主キー：店舗ID

商品テーブル(商品ID、商品名)　主キー：商品ID

にテーブルをバラすことで更新時異状は防げます。

これらが第２正規化されていないテーブルで発生する更新時異状です。

現場でこんなテーブル設計をしているのは今のところ見たことがないのですが、

気をつけましょう！！！

第３正規化されていない(第２正規形のまま)場合に発生する更新時異状

次に第３正規化されていない場合に発生する更新時異状について

具体例で確認します。

上の図は第３正規化されていないテーブルの例で、下はその関数従属性を図にしたものです。

この状態を推移関数従属といい、主キーではない属性とその他の属性に関数従属性がある状態です。

テーブルの例を見ると、

{役職ID：102、役職名：部長}という情報が２箇所、

存在していて冗長です。(「１事実１箇所」(1 fact in 1 place)ではない)

先ほどと同じくこういうデータの持ち方をしてしまうと、

役職名を例えば部長→エンジニア部長に修正したい場合、

同じ修正を何箇所も行わなければなりません。

また１箇所でも修正が漏れると整合性が失われてしまいます。(修正時の更新時異状)

また、

新しい役職{役職ID：104、役職名：係長}追加を計画しており、

まだ所属社員が決まっていないような場合、

社員IDをNULLのまま登録しようとすると主キー制約違反になります。(挿入時の更新時異状)

さらに、{社員ID：004、社員名：斎藤}が退職する場合、

役職情報を一緒に消すわけにはいかず、

社員IDをNULLにして情報を残そうとすると主キー制約違反になります。(削除時の更新時異状)

対策としては

社員テーブル(社員ID、社員名、役職ID)　主キー：社員ID

役職テーブル(役職ID、役職名)　主キー：役職ID

にテーブルをバラすことで更新時異状は防げます。

これらが第３正規化されていないテーブルで発生する更新時異状です。

こちらも現場でこんなテーブル設計をしているのは今のところ見たことがないのですが、

あえて、

社員テーブル(社員ID、社員名、役職ID、役職名)　主キー：社員ID

役職テーブル(役職ID、役職名)　主キー：役職ID

のイメージでレコード登録当時の名称で例で言う役職名を記録して参照できるようにしたり、

例で言う役職テーブルとテーブル結合せずに性能良くSELECTできる工夫をしたり、

などは見たことがあります。

まとめ

今回は正規化不足による更新時異状のパターンについて、

具体例で見ていきました。

ちなみに・・・

この知識はデータベーススペシャリストの試験で良く出題される問題です。

逆に現場では特にこのあたりの話題が出たことはないですが、

みんな当たり前のように更新時異状のないテーブル設計をしている印象です。

僕が９年間データベース周りの設計、性能改善、運用、クエリ作成などしてきて

集めたナレッジをまとめ記事にしたので是非こちらもご覧ください！！

2022年3月21日 初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜