DB設計時に意識すること

概要

DB設計時に意識しておきたいことをまとめました。
アンチパターンを採用しないために一般的な事柄をまとめています。

本記事は「SQLアンチパターン」を参考に作成しています。

ENUMは値が可変となる場合、アンチパターンとなりうるので注意

デメリット

• 値が変更となった場合に、メタデータの変更が必要
• ENUMが取りうるすべての値を確認するには、SQLのメタデータを参照する必要

解決策

• 参照テーブルを用意する
  • 値の変更時はINSERTで対応できる
  • すべての値セットをSELECTで取得できる

まとめ

ENUMの値が可変となる可能性を考慮する。
可変となる場合は、参照テーブルを利用するパターンを採用する。

[reference] SQLアンチパターン P.111 サーティワンフレーバー

DB インデックスの使用を最適化

デメリット

• インデックスを定義しない、闇雲に使用することで無駄なオーバーヘッドが生まれる

解決策

• WHERE, JOIN, ORDER旬が多く利用されるカラムにインデックスを採用する
• 最適なインデックス管理を行うための指標「MENTOR」を採用する

[reference] SQLアンチパターン P.129 インデックスショットガン

ポリモーフィック / EAV (Entity Attribve Value)の利用

ポリモーフィック

1つのコメントテーブルが複数のテーブル（投稿や写真など）を参照する

posts テーブル

id	title	content
1	First Post	This is a post.
2	Second Post	Another interesting post.

photos テーブル

id	image_url	description
1	photo1.jpg	A sample photo.
2	photo2.jpg	Another sample image.

comments テーブル

id	commentable_type	commentable_id	comment
1	Post	1	Great post!
2	Photo	2	Amazing shot!

EAV

属性をレコードとして管理することで柔軟な属性追加を実現

products テーブル

product_id	name
1	T-Shirt
2	Mug

product_attributes テーブル (EAVパターン)

id	product_id	attribute_name	attribute_value
1	1	color	red
2	1	size	M
3	2	material	ceramic
4	2	capacity	300ml

デメリット

• メタデータではなく、アプリケーションコードへの依存が強くなる
• 必須属性を指定できない
• 単一カラムで複数のデータを管理するため、SQLのデータ型を利用できない
• 参照整合性を強制できない

解決策

以下いずれかのパターンを用いる。

• シングルテーブル継承(STI)
• 具象テーブル継承
• クラステーブル継承
• 構造化データ(XML, JSON)

まとめ

ポリモーフィック / EAVパターンはなるべく利用せず、既存テーブルと関連付けるパターンを採用するのが望ましい。

[reference] SQLアンチパターン P.51 EAV（エンティティ・アトリビュート・バリュー）
[reference] SQLアンチパターン P.67 ポリモーフィック関連

隣接リストの利用

階層構造を実現する際、隣接リストを使用したくなるケースがあると思いますが、アンチパターンになり得ます。

categories テーブル

id	name	parent_id
1	Root	NULL
2	子カテゴリ A	1
3	子カテゴリ B	1
4	孫カテゴリ A1	2

デメリット

• 階層が深い場合、多くのJOINが必要になってしまう

解決策

以下パターンの採用を検討する

• 経路列挙（1/, 1/2）
• 入れ子集合
• 閉包テーブル

まとめ

書くパターンのメリ・デメを理解した上で適切に使い分けましょう。

[reference] SQLアンチパターン P.13 ナイーブツリー

最後に

以上です。
自分向けにまとめた記事であるため、少し読みづらいかもしれませんが、ご了承ください🙏