もじれつとがめん

プログラムの忘備録とゲームとかその他色々。

SQLアンチパターン 7章 のメモ

SQL 読書

7章 マルチカラムアトリビュート(複数列属性)

この章で目的しているのは、
1つのテーブルの一つの属性が、複数の値を持つことができるようにする事。

具体的な例としては、ユーザーの複数の電話番号や記事に紐づく沢山のタグ等の属性が考えられる。

アンチパターン : 複数の列を定義する

記事に紐づくタグを複数持てるようにするために、その分複数のカラムを定義するのは、
アンチパターンで非常に良くない上に、かなり多くの問題を抱えている。

例えば、記事に紐づくタグを複数持てるようにするために、
tag1, tag2, tag3 このカラムを持ったarticles テーブルを例として考える

CREATE TABLE articles (
    id PRIMARY KEY,
    title VARCHAR,
    body VARCHAR,
    tag1 VARCHAR(30), -- 問題のタグ1 タグ名が入る
    tag2 VARCHAR(30), -- 問題のタグ2 タグ名が入る
    tag3 VARCHAR(30), -- 問題のタグ3 タグ名が入る
);

解決策について

本に載っている例ならば、紹介されている解決策がスマートで非常に良いとは思うが、 ここで例として考えたアンチパターンに紹介された解決策を当てはめても、あまりスマートではない。

よって、ここでの例をターゲットに適切な解決を考える

中間テーブルを使う

このtag1, tag2, tag3がどのようなモノかを考えると、
記事にタグを付けたいが、複数の記事に同一のタグを付けたい場合は、

  1. 記事は複数のタグを持ち得る
  2. タグは複数の記事を持ち得る

上記の事から、多 対 多 の関係なので、中間テーブルによる関連付けが良さそう。

CREATE TABLE tags (
    id PRIMARY KEY,
    name VARCHAR(30)
);

CREATE TABLE articles_tags (
    id PRIMARY KEY,
    article_id BIGINT UNSIGNED NOT NULL, -- 紐づけたい記事のid
    tag_id BIGINT UNSIGNED NOT NULL, -- 紐づけたいタグのid
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);
-- 複合ユニークキーを作って、記事に同じタグを複数付けられないようにする
ALTER TABLE articles_tags ADD
    UNIQUE KEY unique_key_1(
        article_id,
        tag_id
    );

上記のような中間テーブルを用意すれば、
複数の記事に複数のタグを付けられるし、外部キーをきちんと用意しているので検索するのも比較的簡単にできる。
また、中間テーブルにユニークキーを使うことで1つの記事に同じタグを重複使用することを制限できる。

感想

自分の認識としては、テーブルを定義したときにデータの性質を考えて、切り離せるかどうかを考えるようにしている。
データの性質が違う場合は別テーブルとして考えるようにして、安直に中間テーブルを利用してしまうので、
ケースバイケースで適切に選べるようになりたい。