データベース設計の原則　悪い設計とその解消法

## この記事で書くこと
データベース設計において、悪い設計とその解消法を記述します。
今回紹介する悪い設計は、どのシステムでも一般的に見られるアンチパターン（悪い意味の定番）です。
難しいことは特にありません。基本の仕組みを理解すれば、悪い設計を回避し、適切なテーブル設計を行うことができます。

最後にまとめたチェックリストを作成したので、よければご参照ください。

### 備考

本記事では、現場で役立つシステム設計の原則「6. データベースの設計とドメインオブジェクト」から抜粋し、自分の解釈と具体例を追加しています。

https://gihyo.jp/book/2017/978-4-7741-9087-7

この本ではオブジェクト指向を理論だけではなく、システムにどう落とし込むかを具体的に紹介してくれる良本です。第6章にテーブル設計の内容が記載されています。この章だけでも一読することをおすすめします。

## はじめに
悪いデータベース設計は、プログラムのバグを生む原因となります。データの不整合がプログラムを複雑化し、例外処理や条件分岐の増加によって保守コストが高まり、変更に弱いシステムが生まれます。

悪い設計には、以下のような特徴があります。

-　用途がわかりにくいカラム
-　巨大なテーブル
-　テーブル間の関係がわかりにくい

これらの悪い設計を具体的な事例とともに紹介し、良い設計にするための方法を解説していきます。

## 悪い設計１：用途がわかりにくいカラム

### カラム名が省略形
![スクリーンショット 2023-12-02 21.15.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/5626adc0-b305-1c68-d27a-a43464d18b98.png)

省略されたカラム名では、そのカラムを見ただけでは、カラムが何を意味するのか判断が難しくなります。

例の商品テーブルでは「product_n」「second_n」が省略形となっています。
レコードを見ると「_n」が「name」と推測できますが、テーブル設計書だけをみた人はこの「_n」が「name」なのか「number」なのか、はたまた「note」なのか？と判断することができません。

カラムの意図を明確にするため、明確な単語を使いましょう。


### NULLが入っているカラム
![スクリーンショット 2023-12-02 21.23.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/8f2746d2-35b0-d713-bb49-7e1a029cf2da.png)

NULLは「未知」、「演算不可」、「無効なデータ」を示し、検索クエリにおいて予期しない結果を引き起こすことがあります。

「second_n」を見ると、値が存在するフィールドとNULLのフィールドが混在しています。
「second_n」が空のレコードを抽出したいとき、どういうSQLを書いたらいいのか、実装者は迷うかもしれません。

```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派です。

### 他のカラムの値によって値の意味が変わるカラム
![スクリーンショット 2023-12-02 21.51.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/b0b8d09c-b4e7-add3-0054-d3bd5b52593b.png)

「is_active」は「status」の値によって、意味が変わります。
例えば、以下の表のように商品の状態が定義されているとします。

| status | is_active | 商品の状態       | 
| ------ | --------- | ---------------- | 
| 未発表 | 1         | 未発表で認可済み | 
| 未発表 | 0         | 未発表で未認可   | 
| 販売中 | 0         | 発売中で非表示   | 

`status`が「未発表」のときは、`is_active`は認可済み・未認可の意味を持ちます。
しかし、`status`が「販売中」のときは、`is_active`は表示・非表示の意味を持ちます。

このような依存性は混乱の原因となります。
どのような意図か整理し、どこかにメモしておかなければ、何も知らない人がレコードを見たとき、理解することができません。

解消法は、**１つのカラムには1つの意味しか持たせないこと**です。
is_activeカラムを２つに分割し、「is_authorized（認可フラグ）」 と 「is_visible（表示フラグ）」 という新しいカラムを作成することで、statusの値に関わらず、それぞれのカラムが単一の意味を持たせます。


### カラムから取得した値を、プログラムで分解する必要がある
![スクリーンショット 2023-12-02 22.12.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/c53223de-c509-e7fb-0852-f2b4f4cf3a26.png)

1つのカラムには１つの意味が鉄則です。
「procut_code_version」を見ると、商品コードと商品バージョンが1つのカラムに同席しています。商品コードのみが欲しい場合、SQLクエリもしくはシステムが「-」で値を分離する必要があり、処理が複雑になります。

```sql
-- 商品コードのみを抽出する
SELECT
    SUBSTRING_INDEX(procut_code_version, '-', 1) as 商品コード
FROM
    products
;

-- 結果
+----------+
| 商品コード |
+----------+
| P100    |
+----------+
| P101    |
+----------+
...
```

解消法は「他のカラムの値によって値の意味が変わるカラム」と同じく、1つのカラムに1つの意味しか持たせないことです。

「product_code_version」は「product_code」と「version」に分離させましょう。

### 意味が読み取れないコード（1, 2, 3...などのマジックナンバー）
![スクリーンショット 2023-12-02 22.28.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/1902f6e2-dad8-525f-d5ce-23cd8a7bb40f.png)

「target」は商品がどの層向けかを示すカラムです。
1が男性用、２が女性用、３が子供用としましょう。
このマジックナンバーは数字とその意味をテーブルを見た人が理解できないため、悪い設計となります。

解決策は、ターゲットテーブルを作成し、外部キーをつけることです。

![スクリーンショット 2023-12-02 22.36.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1650886/c5aa7a80-59b4-8733-0329-d021fcbf5fcb.png)


## 悪い設計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.テーブル間の関係
- 外部キー制約が設定されているか？

## まとめ

悪い設計とは何か解説しました。
みんな、悪くしようと思って悪い設計になっているわけではありません。
しかし、良かれと思って扱いづらい設計にならないよう、これらの原則を意識して、良い設計にしていきたいですね！

## 参考

https://gihyo.jp/book/2017/978-4-7741-9087-7


https://zenn.dev/itoo/articles/systems_architecture#6.-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E8%A8%AD%E8%A8%88%E3%81%A8%E3%83%89%E3%83%A1%E3%82%A4%E3%83%B3%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88