実際のデータベース設計ってむずっと思った件について

はじめに

初めまして！
私はいわゆる駆け出しエンジニアですが、先日次の開発のテーブル設計を任させていただきました。
カラム名や正規化、多重度については十分に考えていたつもりでしたが、上長に提出すると主に下記点についてダメ出しばかりくらってしまいました。。。

• サービスの拡張性
  • 後から追加機能を実装する場合、ややこしいことにならないか
• テーブル設計のアンチパターン

RDBを考えるうえで、一つのサービスでも人によってテーブル設計が相違するかもしれませんが、

• 近い未来に考えられる追加機能があった場合、容易に既存システムに組み込めるか？
• そのアンチパターンは適切に使用できているか？（デメリットよりメリットの方が大きいか）
  という点が大事かと感じました。

今回のことがあり、改めてテーブル設計についてまとめてみました。
（間違いあったらすみません！ご指摘いただけると嬉しいです！）

まず正規化について

一つのテーブルから二つ以上のテーブルに分離し、重複や助長な部分をなくすことです
正規化を行うことでデータの一貫性が確保され、効率的な操作が可能になります。
正規化は、第1正規化、第2正規化、第3正規化という3段階の作業に分けられる。

• 第1正規化

お問い合わせテーブル

ID	名前	お問い合わせID	お問い合わせ内容	お問い合わせID	お問い合わせ内容
1	太郎	1	サンプルテキスト1	3	サンプルテキスト3
2	一郎	2	サンプルテキスト2

上記例では、お問い合わせを一件のみ送信した「一郎」の行が途中で終了しています。
データベースでは、基本的にNULL（空欄の行）を許すべきではないという考えのもと、お問い合わせ送信の回数ごとになるよう修正します。

お問い合わせテーブル

お問い合わせ送信者ID	名前	お問い合わせID	お問い合わせ内容
1	太郎	1	サンプルテキスト1
2	一郎	2	サンプルテキスト2
1	太郎	3	サンプルテキスト3

• 第2正規化

お問い合わせテーブル

お問い合わせ送信者ID	名前	お問い合わせID	お問い合わせ内容
1	太郎	1	サンプルテキスト1
2	一郎	2	サンプルテキスト2
1	太郎	3	サンプルテキスト3
1	太郎	4	サンプルテキスト3

「お問い合わせ送信者ID」と「名前」がペアになっていて常に同じ値になっています。
このように主キーとそれ以外のあるカラムがペアになっている場合、複雑で手間のかかる処理が必要になってきてしまいます。
例えば、「太郎」が名前を変更したくなった際、それぞれの行を変更するには相当なめんどくささがあります。（上記テーブルでは少ない行ですが、100行や1000行となった時を考えると恐ろしいですよね、、、）
ってことで、新たなテーブルをもう一つ作成します。

お問い合わせテーブル

お問い合わせ送信者ID	お問い合わせID	お問い合わせ内容
1	1	サンプルテキスト1
2	2	サンプルテキスト2
1	3	サンプルテキスト3
1	4	サンプルテキスト3

利用者テーブル

お問い合わせ送信者ID	名前
1	太郎
2	一郎

• 第3正規化

次に第3正規化では、あるカラムの値が決まると、その他のカラムの値が特定される状態を、さらに分離していきます。
自由度が高いがゆえ必要以上にテーブルを増やしてしまうと、かえって効率が悪くなる可能性もある。
下記例では、あるショッピングサイトのテーブル設計を想定しています。

注文テーブル

注文回数ID	注文者ID	商品ID
1	1	1
2	2	4
3	1	3
4	1	1

利用者テーブル

注文者ID	名前
1	太郎
2	一郎

商品テーブル

商品ID	商品名
1	テスト1
2	テスト2
3	テスト3
4	テスト4

将来的な機能追加も視野にいれる

クライアントと要件定義後をしたものの今後のサービスの拡張性については、開発者側からしたら不明点があったり、あるいはクライアント側でも未来の事だから分からない場合があるかと思います。
そういった時場合でも、ある程度の柔軟性を兼ね備えた設計が求められていると感じました。
※あくまでも未来の可能性の話なので、深く考えすぎてしまうと余計にややこしくなります。

• 例　~あるECサイトにおいて会員種別が見込まれる場合~

会員テーブル

ID	name	email	password
1	aaa	[e-mail]	Aa123456
2	bbb	[e-mail]	Bb123456
3	ccc	[e-mail]	Cc123456

よくある（usersテーブルのような）設計では上記の形が基本形となっていますが、現在の仕様では仮に会員種別を設定したい見込みがある場合、会員種別テーブルと中間テーブルの二つを追加する必要があります。
それでも問題はないかもしれませんが、要求・要件定義時にあらかじめ会員種別導入の可能性があるのであれば、下記のようなパターンも考えられます。

会員テーブル

ID	会員種別ID	name	email	password
1	1	aaa	[e-mail]	Aa123456
2	1	bbb	[e-mail]	Bb123456
3	1	ccc	[e-mail]	Cc123456

会員種別テーブル

ID	role
1	ノーマル
下記が後で追加する種別	↓
2	ブロンズ
3	シルバー
4	ゴールド

最初の開発時は、会員種別IDを全員「１」で登録。表のシステム上では特に変化はないが、裏では会員種別ID = １が会員全員に割り振られている状態。

その後運用していく中で会員種別を追加したくなった場合は、会員種別テーブルに種別を登録。
仮に「ブロンズ」会員の条件を会員登録してから60日経過したらランクアップであった場合、既存会員は会員登録された日付から経過日数を判断して「会員種別ID」を更新する処理を記述する流れとなります。

上記例のような会員種別であったり、他には権限（ロール）やステータス管理、などで活用することが可能です。
またクライアントの要求を満たしながらも、エンジニア側の都合のいいように考えることも大事です。
（コスト面や、テストのしやすさ等）
テーブル設計はあまり経験する機会がなく、いろいろな考え方もできるので、機会があれば積極的に挑戦して経験を積んでいきたいですね！

アンチパターンって？？

• 長期的な保守性やパフォーマンスに問題を引き起こすような設計のことを、いわゆるアンチパターンと呼んでいます。一見問題なさそうな設計であっても、クエリの複雑性や保守性など、様々な不都合を引き起こしかねません。そのため、設計段階で入念な計画が不可欠です。

テーブルのアンチパターン例

• 正規化の過剰

ID	name_id	email_id
1	1	1

ID	name
1	太郎

ID	email
1	[e-mail]

過度に正規化すると、テーブル間の結合が増え、パフォーマンスが低下することがある。データ取得が複雑になり、開発や保守が困難になります。

• NULLの乱用

ID	name	email	address	phone_number
1	太郎	[e-mail]	null	null
2	一郎	null	null	000-0000-0000

そもそもテーブル設計において、NULL値を許容しないことが原則となっています。
クエリロジックの複雑性やバグの原因になりかねないためです。
またDBは事実を記録するという考えのもと、そこに空欄があるのもおかしな話ですよね？
解決策としては、

• NULLを許容してもいいテーブル（オプションテーブル）を作成する。

• NULLを許容しないようなテーブル（オプションテーブル）を作成する。
  上記二パターンが想定されます。個人的には後者のほうがいいかなと思っています。

• 他

他には複雑な外部キー制約や、EAVモデル (Entity-Attribute-Value) がなど該当します。

• 複雑な外部キー制約を回避するにはリレーションで、あるテーブルから対象のテーブルまで適正な順で追えるかどうかを考えてみるといいです。
• EAVモデルは、特にvalueにおいて型制限が定まっていないことにより、複雑性やデータの整合性が確保されない点が問題となっているので使用する際は今一度別のアプローチを考える必要があります。

型のアンチパターン例

• 汎用的なデータ型の使用

文字列をTEXTやVARCHAR（MAX）で保存
必要以上にデータが多くなり、パフォーマンスの低下を招く。
また制約も緩いため、そのカラムに対する妥当な最大数を指定する必要がある。

日付を文字列型（VARCHARなど）で保存
現実的にありえない日付が保存される可能性や、扱いが複雑になるため、訂正な型（DATE）を推奨する。

• ENUM型

特定の値をリストから選択する場合に便利で使いたくなってしまいますよね。
問題点としては、新しい値を指定したくなった際にスキーマの変更が必要になるため、メンテナンスに手間がかかり複雑性を生む原因になる。

最後に

例はあくまでも一例にすぎませんので、他にも色々あります！
正規化や多重度についてはテーブル設計でよく言われることですが、私自身実際に経験してみると、「あ、それだけじゃないんだ」となんとなく分かってはいたものの力不足を感じました。
もっと精進してまいります！！！