この記事で書くこと
データベース設計において、悪い設計とその解消法を記述します。
今回紹介する悪い設計は、どのシステムでも一般的に見られるアンチパターン（悪い意味の定番）です。
難しいことは特にありません。基本の仕組みを理解すれば、悪い設計を回避し、適切なテーブル設計を行うことができます。
最後にまとめたチェックリストを作成したので、よければご参照ください。
備考
本記事では、現場で役立つシステム設計の原則「6. データベースの設計とドメインオブジェクト」から抜粋し、自分の解釈と具体例を追加しています。
この本ではオブジェクト指向を理論だけではなく、システムにどう落とし込むかを具体的に紹介してくれる良本です。第6章にテーブル設計の内容が記載されています。この章だけでも一読することをおすすめします。
はじめに
悪いデータベース設計は、プログラムのバグを生む原因となります。データの不整合がプログラムを複雑化し、例外処理や条件分岐の増加によって保守コストが高まり、変更に弱いシステムが生まれます。
悪い設計には、以下のような特徴があります。
- 用途がわかりにくいカラム
- 巨大なテーブル
- テーブル間の関係がわかりにくい
これらの悪い設計を具体的な事例とともに紹介し、良い設計にするための方法を解説していきます。
悪い設計１：用途がわかりにくいカラム
カラム名が省略形
省略されたカラム名では、そのカラムを見ただけでは、カラムが何を意味するのか判断が難しくなります。
例の商品テーブルでは「product_n」「second_n」が省略形となっています。
レコードを見ると「_n」が「name」と推測できますが、テーブル設計書だけをみた人はこの「_n」が「name」なのか「number」なのか、はたまた「note」なのか？と判断することができません。
カラムの意図を明確にするため、明確な単語を使いましょう。
NULLが入っているカラム
NULLは「未知」、「演算不可」、「無効なデータ」を示し、検索クエリにおいて予期しない結果を引き起こすことがあります。
「second_n」を見ると、値が存在するフィールドとNULLのフィールドが混在しています。
「second_n」が空のレコードを抽出したいとき、どういうSQLを書いたらいいのか、実装者は迷うかもしれません。
-- 空文字？
SELECT * FROM products WHERE second_n = "";
-- IS NULLも含めるべき?
SELECT * FROM products WHERE second_n = "" OR second_n IS NULL;
NULLが含まれるカラムはSQLの複雑化を招きます。
また、SQLの書き方を間違えると意図したレコードを抽出できない可能性があります。
そのため、NULLを入れなくて良い箇所はNOT NULL制約をつけるのが望ましいです。
また、正規化を行うこともNULLが混じるカラムを減らす有効な手段です。
もちろん、すべてのカラムでNULLがダメなわけではありません。
削除日付や外部キーをもつカラムなどで適切にNULLを使うことは問題ありません。
※NULL不要問題は人によって考え方が違うことが多いですが、私は適切なNULLならOK派です。
他のカラムの値によって値の意味が変わるカラム
「is_active」は「status」の値によって、意味が変わります。
例えば、以下の表のように商品の状態が定義されているとします。
| status | is_active | 商品の状態 |
|---|---|---|
| 未発表 | 1 | 未発表で認可済み |
| 未発表 | 0 | 未発表で未認可 |
| 販売中 | 0 | 発売中で非表示 |
statusが「未発表」のときは、is_activeは認可済み・未認可の意味を持ちます。
しかし、statusが「販売中」のときは、is_activeは表示・非表示の意味を持ちます。
このような依存性は混乱の原因となります。
どのような意図か整理し、どこかにメモしておかなければ、何も知らない人がレコードを見たとき、理解することができません。
解消法は、１つのカラムには1つの意味しか持たせないことです。
is_activeカラムを２つに分割し、「is_authorized（認可フラグ）」 と 「is_visible（表示フラグ）」 という新しいカラムを作成することで、statusの値に関わらず、それぞれのカラムが単一の意味を持たせます。
カラムから取得した値を、プログラムで分解する必要がある
1つのカラムには１つの意味が鉄則です。
「procut_code_version」を見ると、商品コードと商品バージョンが1つのカラムに同席しています。商品コードのみが欲しい場合、SQLクエリもしくはシステムが「-」で値を分離する必要があり、処理が複雑になります。
-- 商品コードのみを抽出する
SELECT
SUBSTRING_INDEX(procut_code_version, '-', 1) as 商品コード
FROM
products
;
-- 結果
+----------+
| 商品コード |
+----------+
| P100 |
+----------+
| P101 |
+----------+
...
解消法は「他のカラムの値によって値の意味が変わるカラム」と同じく、1つのカラムに1つの意味しか持たせないことです。
「product_code_version」は「product_code」と「version」に分離させましょう。
意味が読み取れないコード（1, 2, 3...などのマジックナンバー）
「target」は商品がどの層向けかを示すカラムです。
1が男性用、２が女性用、３が子供用としましょう。
このマジックナンバーは数字とその意味をテーブルを見た人が理解できないため、悪い設計となります。
解決策は、ターゲットテーブルを作成し、外部キーをつけることです。
悪い設計2：巨大なテーブル
カラム数が多い巨大なテーブルはプログラムを複雑にします。
どの程度が多い、と感じるかは感覚の部分もありますが、私はカラムが２０個以上だと多いと感じます。
（実際に会計アプリでカラムが５０個以上あるテーブルを扱ったことがありますが、読み込むのがめちゃ大変でした）
カラム数が多いと
- 似たようなカラムが多く、使い分けがわからない
- NULL値が多い
のような問題が発生し、テーブルを理解することが大変になります。
巨大なテーブルの解決策は、とにかく正規化を適切に行うことです。
また、適切な制約をつけることが大切です。
特に大事な制約たち
NOT NULL制約
カラムにはNULLを含めないのが、データベース設計の基本です。NULLを使うと、SQLやプログラムでNULLを想定する必要が生じます。
意図しない挙動はバグの温床です。
NULLにする明確な意図がない場合、基本的にNOT NULLにすることが望ましいです。
UNIQUE(一意性)制約
データの重複を防ぎます。UNIQUE制約は複数のカラムにもつけることができます。
外部キー制約
テーブル間の関係を明確にしてくれます。
外部キーのいいところは、接続先に確実に値があると保証されることです。
個人的な感想ですが、DBerverなど自動でER図を作成してくれるツールは、外部キーがないとER図のテーブルを繋いでくれないため、不便と感じました。外部キーはつけましょう！（切実）
悪い設計3：テーブル間の関係がわかりにくい
- 外部キー制約がない
- キーとなるカラム名に一貫性がない
ようなテーブル設計は、テーブルの関係を理解するのに時間がかかる悪い設計です。
重なりますが、外部キーはつけるようにしましょう。
キーとなるカラム名に一貫性がない
商品テーブルの主キーを考えたとき「id」「product_id」などが思いつくと思います。
また、商品テーブルにカテゴリテーブルが外部キーとして紐づく場合、「category」「category_id」などが考えられます。
これらをどちらにするかはテーブルごとに命名方法が異なると、これまた見た人は混乱します。
テーブル、カラムの命名をどう設定するかはチーム、案件ごとに異なりますが、大切なのは一貫して自分たちが決めたルールを守ることです。
チェックリスト
これらの内容をまとめて、データベース設計チェックリストを作成しました。
1.カラム名の明確性
- すべてのテーブル名とカラム名は明確かつ省略されていないか？
- カラム名からその目的や内容が直感的に理解できるか？
2.NULL値の使用
- 不必要にNULLを許容していないか？
- NOT NULL制約が必要なカラムに適用されているか？
3.カラムの依存性
- カラムの値は他のカラムの値に依存せず、単一の意味を持っているか？
4.カラムのデータ型と値
- 各カラムのデータ型は適切か？（例: 文字列、数値、日付等）
- 「マジックナンバー」を避け、意味のある値や参照が使われているか？
5.テーブルのサイズと構造
- テーブルは適切なサイズか？（過度に巨大でない、似たようなカラムが過多でない）
- 必要に応じて正規化が行われているか？
- 重複データの発生を防ぐために、適切な場所でUNIQUE制約が設定されているか？
6.テーブル間の関係
- 外部キー制約が設定されているか？
まとめ
悪い設計とは何か解説しました。
みんな、悪くしようと思って悪い設計になっているわけではありません。
しかし、良かれと思って扱いづらい設計にならないよう、これらの原則を意識して、良い設計にしていきたいですね！
参考