【随時更新】テーブル設計でミスらないために確認したいアンチパターン

はじめに

参考書籍が非常に参考になったのでテーブル設計に関する内容のみをピックアップまとめてみました。普段テーブル設計をしていれば"当たり前"に実践している方も多いと思いますが、今回チーム内での勉強会用の資料の意味合いも込めて作成しました。本記事では、基本的にリレーショナルデータベースにおける設計を想定しています。

ご留意ください
本記事は"何があってもこのような設計が非推奨される"というものではありません。その時々のコンテキストによっては採用することが妥当な場合もあるかと思います。

1. 正規化が不十分

非正規化とは、データベース設計において、データの重複や冗長性を意図的に許容することを指します。正規化は、データの整合性と効率的なストレージのためにデータの重複を排除するプロセスですが、非正規化はそれとは逆のアプローチをとります。非正規化の目的は主にパフォーマンスの向上です。ジョイン操作の回数を減少させるなどの利点があります。

非正規化テーブル

学生ID	学生名	講座ID	講座名	教師名
1	田中太郎	1	数学	佐藤先生
2	山田花子	1	数学	佐藤先生
2	山田花子	2	歴史	鈴木先生

どうしてダメなのか

1. データの冗長性 :
   「山田花子」や「数学」などのデータが繰り返し登場し、不要なストレージを消費する。

2. 更新の問題 :
   佐藤先生が数学から別の講座に移動した場合、複数の行を変更する必要がある。

3. データの矛盾 :
   教師名を一箇所のみ更新すると、同じ講座でも教師名が異なる場合が発生する。

改善ポイント

1. 正しく正規化を行う :
   学生、講座、教師をそれぞれ独立したテーブルに分けることで、冗長性を排除する。

2. 最低でも第三正規化を行う :
   学生と講座の関係、講座と教師の関係を明確にし、データの整合性を保つため。

2. 非スカラ値/ジェイウォーク

非スカラ値とは、データベースのカラム内で複数の値を持つことができるデータ型や構造を指します。たとえば、カンマで区切られた文字列や配列、JSONオブジェクトなどがこれに該当します。通常のリレーショナルデータベースの設計原則では、カラムに一つのアトミック（不可分）な値を持たせることが推奨され、非スカラ値は避けるべきとされます。

user_id	user_name	phone_numbers(ここが非スカラ値)
1	Taro	"080-1234-5678,090-9876-5432"
2	Hanako	"070-1111-2222"

どうしてダメなのか

1. クエリの複雑さ増加:
   特定の電話番号を持つユーザーを検索する際に、文字列の中から部分一致を行わないといけない。

2. 正規化の原則違反:
   一つのカラムに複数の電話番号が格納されており、アトミック（不可分）でない。

3. インデックスの利用困難:
   非スカラ値の部分に対する効率的なインデックスが作成できない。

改善ポイント

1. 正規化を行う: phone_numbersを独立したテーブルとして切り出し、user_idをキーとして関連付ける。

3. ダブルミーニング

「ダブルミーニング」は、一つのデータベースカラムに複数の意味を持たせるテーブル設計のアンチパターンです。この方法は、データの一貫性や解釈の難しさを引き起こします。例として、あるテーブルで「column_1」が当初体重を示していたが、後に年齢を示すように変更された以下のような場合です。

id	column_1
1	65.5(この行は体重)
2	28(この行は年齢)

どうしてダメなのか

1. データ一貫性の喪失:
   同じカラムで体重と年齢を混在させているため、データの意味が不明瞭。

2. 拡張性・維持困難:
   今後のデータ追加や修正が難しくなり、データベースの信頼性が低下。

3. クエリ複雑化:
   データの取得やフィルタリング時に意味を判別するための追加のロジックが必要。

改善ポイント

1. カラム分割:
   「体重」と「年齢」のための独立したカラムをそれぞれ作成する。

2. 汎用的・抽象的な命名は避ける:
   カラム名を具体的に（例: weight、age）とすることで意味の曖昧さを排除。

4. EAV(Entity-Atribute-Value)

上記のダブルミーニングと同様に、複数の目的に使われるカラムを用意する設計です。以下のテーブルでは、属性と値に柔軟に任意の値を格納することができます。以下は、EAV設計の具体的なテーブル例です。

テーブル名: EAV

エンティティID	属性	値
1	名前	田中太郎
1	年齢	25
2	名前	山田花子
2	趣味	映画鑑賞
3	名前	佐藤一郎
3	住所	東京都世田谷区

どうしてダメなのか

1. 必須属性の指定不可:
   すべてのエンティティが「住所」属性を持っているべきか、または「趣味」を持っているべきかが明示的にはわかりません。

2. データ型の指定不可:
   「年齢」が数値であるべきか、文字列であるべきかが定義できない。このため、「年齢」に「二十五」という文字列が入る可能性があります。

3. 外部キーの設定不可:
   「趣味」が別のテーブルに存在する趣味リストから参照されるべき情報である場合、この設計ではその参照制約を設けることができません。

改善ポイント

1. カラム・テーブル分割
   「名前」や「年齢」のような独立した属性は専用のカラムとして表現する。正規化されたテーブルで分割します。

2. 汎用的・抽象的な命名は避ける:
   属性, 値のような抽象的なカラム名ではなく、具体的な名称を使用します。

3. データ型の制約:
   年齢のようなカラムには数値型を指定し、データの一貫性を確保します。

5. 一つのIDに複数の意味を持たせる

テーブル設計において、例えば、IDカラムに最初の冒頭2文字は支店コード、真ん中2文字は部署コード、残り3文字は担当コードと言ったように複数の意味を持たせる意味を含んだID、あるいは論理ID, スマートカラムと呼ばれるものがあります。

テーブル名: Employees

ID	Name	Address
AB01XYZ	Tanaka	Tokyo
AB02ABC	Suzuki	Kyoto
CD03DEF	Sato	Osaka
CD04UVW	Yamada	Hokkaido

ここで、IDカラムの解釈は以下のようになります。

• AB01XYZ:

  • 支店コード: AB
  • 部署コード: 01
  • 担当コード: XYZ

• CD04UVW:

  • 支店コード: CD
  • 部署コード: 04
  • 担当コード: UVW

どうしてダメなのか

1. データ一貫性の喪失:
   あるレコードでは支店コードの部分に実際の支店情報ではなく誤った値が入っている可能性があります。これにより、解釈時に誤解が生じる可能性が高まります。

2. 拡張性・維持困難:
   部署コードが3文字になるような変更が必要になった場合、既存のIDのフォーマットとの整合性をとるのが難しくなります。

3. クエリ複雑化:
   支店コードだけを抽出する場合、SQLクエリでSUBSTRINGなどの関数を用いて操作する必要があります。これにより、クエリの読み込み性が低下し、性能も低下する可能性があります。

改善ポイント

1. 意味ごとにカラムを分割する:
   1つのIDカラムに含まれている各情報を、それぞれのカラムに分割します。

2. 関連する情報を別テーブルに移動する:
   まとまった情報の塊（例: 部署コードと部署名）があれば、それを新たなテーブルに分離、外部キー制約を使用して確実に結びつけます。

6. ポリモーフィック関連

複数のエンティティまたはテーブルへの参照を一つのカラムで持つテーブル設計パターンです。これにより、データの整合性やクエリの複雑さが問題となることがあります。以下の例では、reference_idは記事や商品のIDを指し、typeでそのIDが何を指しているかを示しています。

テーブル: comments

comment_id	reference_id	type
1	10	article
2	5	product

どうしてダメなのか

1. クエリの複雑さ増加:
   typeに基づいて適切なテーブルに結合するための条件分岐が必要。

2. 参照整合性保証不可:
   外部キー制約が設定できず、正しくリレーションを保てないリスクが高まる。

改善ポイント

1. カラム分割: article_idやproduct_idといった専用カラムを作成して分割する。
2. 外部キー制約の活用: 分割後の各参照カラムに対して、適切な外部キー制約を設定する。
3. 共通テーブルの利用: コメント可能なエンティティ用の共通テーブルを検討し、そのIDで参照する。

7. 列持ちテーブル/マルチカラムアトリビュート

列持ちテーブルは、一つのエンティティの異なる属性や関連エンティティを複数の列として持つ設計方法です。例えば、社員の子供の名前を「子1」「子2」のように列で持つテーブルがこれに該当します。この設計は直感的でシンプルですが、拡張性やデータの疎密性、クエリの複雑さの面で問題点を持つとされています。このテーブルでは、各社員が持つ子供を「子1」「子2」「子3」という列で表現しています。

社員ID	社員名	子1	子2	子3
001	山田	一郎	二郎	三郎
002	鈴木	健太	寛太

どうしてダメなのか

1. 拡張性の欠如:
   新たな列（例：子4, 子5）が必要になった際、テーブル構造を変更する必要が出てきます。

2. データの非効率性:
   一部のレコードだけが利用する列が存在することで、データの疎密性が生じ、ストレージの無駄が発生します。

3. クエリの複雑性:
   特定の列の値を検索や集計する際、複数の列を対象とする必要が出てきて、クエリが複雑になります。

改善方法

1. 行持ちテーブルへの変換:
   複数の「子」列を持つテーブルを行持ちの形式に変更します。各「子」を独立した行として表現し、子供の番号や属性を別のカラムとして持つようにします。

8. カーディナリティの低いインデックス

カーディナリティが低いインデックスとは、そのインデックスのユニークな値の数が少ないことを指します。例えば、以下のテーブルの性別のように「男性」「女性」の2つの値しか持たないフィールドにインデックスを作成する場合、カーディナリティが非常に低いと言えます。

従業員ID	氏名	性別
1	田中太郎	男
2	山田花子	女
3	鈴木一郎	男
4	佐藤恵美	女
5	渡辺和也	男

どうしてダメなのか

1. インデックスの効果が弱い :
   ユニークな値のバリエーションが少ないため、インデックスを通してデータを検索する際の効果が薄い。多くのレコードが同じインデックス値を持つため、検索が効率的になるとは限らない。

2. ストレージ消費 :
   インデックスはストレージスペースを使用します。カーディナリティの低いインデックスは効果が薄い割にスペースを占めるため、無駄となる可能性が高い。

3. 更新遅延 :
   データの追加や更新の度にインデックスも更新される必要があります。効果の薄いインデックスが存在すると、これがデータベースのパフォーマンスに影響を与える可能性があります。

改善ポイント

1. ビットマップインデックスの利用:
   一部のデータベース管理システムでは、カーディナリティの低いカラムに特化したインデックスとしてビットマップインデックスを提供している。

2. パーティショニングの利用:
   データベースのテーブルを特定のカラムの値に基づいて複数の小さなテーブルに分割することで、検索性能を向上させることが可能。

3. 複合インデックスの検討:
   単独のカーディナリティが低いカラムではなく、複数のカラムを組み合わせた複合インデックスを利用する。

9. キーレスエントリ(外部キー嫌い)

外部キーは、リレーショナルデータベースのテーブル設計において、あるテーブルのカラムが他のテーブルの主キーを参照する際の制約やリンクとして使用されるキーです。外部キーの主要な目的は、データの整合性を保持することです。また、RDBMSにもよりますが、外部キーにインデックスが付与されていない場合、JOIN時にパフォーマンスが悪化する可能性もあります。

どうしてダメなのか

1. クエリパフォーマンスの低下:
   外部キーを使用して他のテーブルとJOINする際、インデックスがないとフルテーブルスキャンが必要になることが多く、クエリの実行時間が大幅に増加する原因となる。

2. 外部キー制約の遅延:
   外部キーは、関連するテーブル間の整合性を保つための制約です。インデックスがない場合、レコードの追加や更新、削除時に、制約の確認作業が遅くなります。

3. データ整合性のリスク:
   インデックスはデータの整合性を助けるツールとしても機能します。特に、リレーショナルデータベースにおいて、関連するテーブル間の関連性を迅速に確認し、データの不整合を防ぐ役割があります。

改善ポイント

1. 外部キー制約の利用:
   例えば、MySQLではFOREIGN・REFERENCESキーを用いて外部キーを付与することができます。さらに、外部キーを設定すると、自動的にインデックスが作成されます。

2. インデックスの有無:
   コマンドを用いてインデックスの有無を確認します。MySQLにおける、SHOW INDEXを利用すること該当カラムにインデックスが貼られているか確認できます。

3. EXPLAINの活用:
   テーブル同士をJOINする場合、EXPLAINを活用、key列に当該インデックスがあること、かつ、rows列に読み込まれている行数が小さい(絞り込めている)ことを確認します。

手前味噌ではありますが、EXPLAINを使ったチューニングについては以下の記事でハンズオン形式で解説しているので参考にしてみてください。

10. 全てのカラムにインデックスを貼る/インデックスショットガン

この手法は、テーブルのすべてのカラムにインデックスを貼るという、かなり極端なテーブル設計アプローチを指します。一見すると、これにより任意のカラムでの検索が高速化されるように思えるかもしれませんが、実際には多くのデメリットが存在します。

どうしてダメなのか

1. 書き込みの遅延 :
   INDEXが多いと、データのINSERTやUPDATE時にすべてのインデックスも更新されるため、書き込みが遅くなる。

2. ストレージの無駄 :
   不要なインデックスはストレージを無駄に消費し、データベースのサイズが不必要に大きくなる。

3. 管理の複雑化: どのインデックスが実際に使用されているのか、どのインデックスが不要なのかを判断するのが難しくなります。また、新しいインデックスの追加や既存のインデックスの削除・変更の際も複雑さが増します。

改善ポイント

1. 必要なカラムだけにINDEXを貼る :
   必要の無いINDEXは作成せず、不要なオーバーヘッドを削減し、パフォーマンスを最適化します。

2. 最適なインデックス戦略の策定 :
   EXPLAIN句などを用いパフォーマンスを分析して、効果的なインデックスのみを維持し、不要なものは削除します。

11. 履歴情報が保持されていない

こちらはケースバイケースですが、過去の変更履歴が必要になった際にやりとりが消失してしまっている例になります。データの変更履歴を維持しない設計に伴い。この場合、新しいデータが追加または変更されるたびに、古いデータが上書きされてしまいます。

変更前

顧客ID	名前	住所	電話番号
1	田中	東京, 中央区	03-1234-5678
2	鈴木	大阪, 難波	06-2345-6789

変更後

顧客ID	名前	住所	電話番号
1	田中	東京, 新宿区	03-1234-5678
2	鈴木	大阪, 難波	06-2345-6789

この上書き操作により、田中さんの元の住所である東京, 中央区の情報は完全に失われてしまいます。このような設計では、以前の情報を取得することは不可能となります。

どうしてダメなのか

1. データの回復が困難: 何らかの理由で過去のデータを参照または復元する必要が生じた場合、上書きされたデータは取り戻すことができない。

2. 監査の難しさ: データの変更履歴がないため、いつ誰が何を変更したか、なぜ変更したかを追跡することが難しくなる。これは、ビジネスや法的要件において監査トレイルを持つことが求められる場合に問題となります。

3. 誤操作の影響: ユーザーが誤ってデータを変更または削除した場合、その操作を元に戻すことが難しくなる。

改善ポイント

1. 履歴テーブルの導入:
   変更されたデータのバージョンや状態を保持する専用の履歴テーブルを作成します。主テーブルのデータが変更されるたび、関連する情報を履歴テーブルに追加することで、過去のデータを追跡できるようにします。

参考書籍