Zenn
🔰

RDBで多対多のテーブルを作るときに気をつけること

2024/05/30に公開
MySQL
RDB
tech

MySQLにおける多対多の関係と、少し原則から外れたパターンについて

多対多の関係をデータベースで管理する場合、中間テーブルを使用するのが一般的です。
中間テーブルには関係するテーブルのIDのみを保存するのが原則とされていますが、特定のユースケースにおいては追加情報を保存することが合理的です。
ここでは、その詳細と最適な設計方法について解説します。

中間テーブルの基本設計

まず、中間テーブルの基本形を確認します。例えば、ユーザーとプロジェクトの多対多の関係を管理する場合、以下のような中間テーブルを作成します。

CREATE TABLE User_Project (
    user_id INT,
    project_id INT,
    PRIMARY KEY (user_id, project_id),
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (project_id) REFERENCES Projects(id)
);

この基本形では、User_Project テーブルはユーザーとプロジェクトの関係をシンプルに管理します。
Railsなどでよく見るパターンです。

追加情報も保存したい！

多対多の関係において、関係自体に対して追加情報を保存することが求められる場合があります。
例えば、ユーザーがプロジェクトに参加する日付やプロジェクト内での役割を保存する場合、中間テーブルにこれらの情報を追加することが適切です。

CREATE TABLE User_Project (
    user_id INT,
    project_id INT,
    join_date DATE,
    role VARCHAR(255),
    PRIMARY KEY (user_id, project_id),
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (project_id) REFERENCES Projects(id)
);

他には、顧客が製品を購入する履歴を管理する場合、購入日や数量などの情報を中間テーブルに保存します。

CREATE TABLE Customer_Product (
    customer_id INT,
    product_id INT,
    purchase_date DATE,
    quantity INT,
    PRIMARY KEY (customer_id, product_id, purchase_date),
    FOREIGN KEY (customer_id) REFERENCES Customers(id),
    FOREIGN KEY (product_id) REFERENCES Products(id)
);

設計のポイント

正規化とパフォーマンスのバランス

中間テーブルに追加情報を保存することは、正規化の原則に反する場合があります。
しかし、パフォーマンスや実際の運用面を考慮すると、適切な設計といえます。

クエリパフォーマンスを最適化するためには、EXPLAIN を使用しましょう。
EXPLAIN を使用すると、MySQLがクエリをどのように実行するかを確認できます。
これにより、インデックスの有無やテーブルスキャンが行われているかどうかを把握でき、パフォーマンスのボトルネックを特定するのに役立ちます。

EXPLAIN の使用例

以下は、ユーザーとプロジェクトの関係を取得するクエリに対して EXPLAIN を使用する例です。

EXPLAIN SELECT u.name, p.title
FROM Users u
JOIN User_Project up ON u.id = up.user_id
JOIN Projects p ON up.project_id = p.id;

この結果には、クエリの実行計画が表示され、各テーブルへのアクセス方法やインデックスの使用状況が示されます。これに基づいて、必要に応じてインデックスを追加したり、クエリを最適化したりできます。

インデックスの追加

EXPLAIN を用いてテーブルスキャンが行われていることがわかった場合、適切なインデックスを追加することでパフォーマンスを大幅に向上させることができます。
例えば、User_Project テーブルにインデックスを追加する場合は以下のようにします。

CREATE INDEX idx_user_project_user_id ON User_Project(user_id);
CREATE INDEX idx_user_project_project_id ON User_Project(project_id);

これにより、User_Project テーブルの user_id および project_id カラムに対する検索が高速化されます。

継続的なモニタリング

クエリパフォーマンスの最適化は一度きりの作業ではありません。
アプリケーションの使用状況が変わるとクエリのパフォーマンスも変わるため、定期的に EXPLAIN を使用してクエリを監視し、必要に応じて調整を行うことが重要です。
EXPLAIN を活用することで、効率的でスケーラブルなデータベース設計を維持し、最適なパフォーマンスを確保できます。

適切なインデックスの設定

複数のカラムを持つ中間テーブルには、適切なインデックスを設定することでクエリパフォーマンスを向上させます。
多対多の中間テーブルはレコード数が多くなりがちなので、クエリパフォーマンスに対する意識を常に持ちましょう。

関係の整合性の維持

外部キー制約を使用して、関係するテーブル間の整合性を維持します。これにより、データの一貫性を保つことが可能です。
特に主キーに id とせず、組み合わせとすることは原理原則です。
特別な理由がない限り守りましょう。現場のルールによっては論理削除であったり、常に主キーを id とするケースがあります。
この場合、現場のルールに合わせるかどうかをチームメンバーに相談しましょう。

まとめ

  • 多対多の関係において、中間テーブルには関係するテーブルのIDのみを保存するのが原理原則
  • 組み合わせの関係性においてのみ存在する情報を中間テーブルに保存することは許容できる設計
  • パフォーマンスが悪くなるケースを想定し、作成時点で想定されるクエリの Explain は出しておく
  • 現場のルールに合わせて外部キー制約を外したり、主キーを別にすることはあるが、整合性を保つためのコードが必要になる

Discussion