初めに
基本情報技術者試験の勉強をしていた時に、個人的に納得できなかったものが第3正規形の問題です。試験問題の解法自体は理解するものの、用意されたデータがあからさますぎで実際の設計時はどうなるのだろうと疑問に思っていました。具体的に第3正規形におけるデータとはどのようなものでしょうか?
正規化のおさらい
第1正規形
・繰り返し項目を持たない
・導出項目を持たない
第2正規形
・第1正規形を満たしている
・主キーに対してすべての非キー属性が完全関数従属
第3正規形
・第2正規形を満たしている
・すべての非キー属性がどの候補キーに対しても推移的に関数従属していない
LPI-Japan OSS-DBのサイトに載っている正規化の説明から一部流用します。
第3正規形の問題でよくあるもの
個人的に納得できなかった問題のサンプルがこれです。
平成29年春期問27 どのレベルまでの正規形か｜応用情報技術者試験.com
- 明らかに「商品コード」が主キー扱いでいいのに主キーじゃない
- そもそも「商品コード」ってこの時点で必要？
上記の問題を見ているとこんなことをよく思ってしまいます。
業務におけるXXコード
実運用上すでに存在しているもの
システムの「主キー」でも、「外部キー」でも無いものに「XXコード」「YY番号」「ZZ_ID」のような識別子のような属性は付与されることはあります。
- ITシステム化される前の運用で利用されていた属性
- 商品を特定するコードという用途以外に、製造ロットなどの意味合いが含まれている等
- ITシステム化される前の古いコードで、一意性に欠ける。互換性のため用意等
- 自身のシステムでは「非キー属性」となるが、外部システムでは「主キー」として取り扱う属性
- 製造会社のシステムでは「主キー」。商品を一意に特定できるように設計。
- 卸売会社のシステムでは「非キー属性」。各製造会社毎の独自ルールなので「主キー」にならない。
上記の理由などで、「主キー」のような属性でも「非キー属性」であることは珍しくありません。そのため
Q: 明らかに「商品コード」が主キー扱いでいいのに主キーじゃない
A:「主キー」のように見えるものが、「非キー属性」の場合もある
Q: そもそも「商品コード」ってこの時点で必要？
A: 人工キーでなく、実運用上で存在している場合は必ず必要
となります。
システム化する際に付与付与する
こちらは第3正規化をする前提でキーを付与するという想定ですね。例えばシステムを利用するユーザのテーブルがあるとします。
ユーザテーブル
| ユーザID | 氏名 | 都道府県 | 市区町村 | 住所 |
|---|
「都道府県」「市区町村」などは
- 入力フォームをドロップダウンリストにして入力ミスを防止
- 利便性向上のための予測変換機能
- 市区町村の統廃合によるデータ更新など
これらのことを考え、別テーブルに分けると考えます。その場合
ユーザテーブル
| ユーザID | 氏名 | 都道府県コード | 都道府県 | 市区町村コード | 市区町村 | 住所 |
|---|
「都道府県コード」「市区町村コード」を追加し、その後第3正規化するということが考えられます。
ユーザテーブル
| ユーザID | 氏名 | 都道府県コード | 市区町村コード | 住所 |
|---|
都道府県テーブル
| 都道府県コード | 都道府県 |
|---|
市区町村テーブル
| 市区町村コード | 市区町村 |
|---|
第3正規形のデータの実装パターン
実際に第3正規形にできる場合、テーブル分割することが考えられます。ただ、テーブルを分割しないパターンも存在します。
第3正規化を実施する
これは「システム化する際に付与付与する」で出てきたものですね。ある程度データ量がある場合は第3正規化したほうが良いので、素直にテーブルを分けます。特に
- 都道府県/市区町村
- 郵便番号
等は第3正規形にし、変更があった場合は上記サイトからダウンロードしてDBを更新する等の運用が可能です。
CHECK&ENUMなどを利用する
あまりデータパターンがない場合は、第3正規化を実施せずにDBのCHECK制約やENUMなどを利用することもあります。
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
CREATE TABLE shirts (
name VARCHAR(40),
size VARCHAR(10) CHECK( size IN ('x-small', 'small', 'medium', 'large', 'x-large') )
);
ORM(ActiveRecord等)の機能を利用する
CHECK&ENUMはDBの実装に依存するため、ORM側で実装することもあります。たとえばORMのActiveRecordではenum機能があり、これでデータパターンを制限することが可能です。
class Conversation < ActiveRecord::Base
enum status: [ :active, :archived ]
end
# conversation.update! status: 0
conversation.active!
conversation.active? # => true
conversation.status # => "active"
# conversation.update! status: 1
conversation.archived!
conversation.archived? # => true
conversation.status # => "archived"
最後に
第3正規形における具体的なデータや、その実装について解説しました。特に実装部分に関しては、書籍「SQLアンチパターン」の「サーティワンフレーバー」なども参考になるのでお勧めです。第3正規化を実施する以外のパターンも紹介しましたが、一部は慎重に利用すべきもの(ENUM等)もあるので注意してください。