メディアリレーションチームの開発リーダーをしている永井です。12月5日にメディアリストの管理画面をリニューアルしました。リニューアルに伴い、メディアに紐付くカテゴリの改善があったのですが、テーブル構成が非正規化でありいくつか問題がありました。今回は、非正規化だったテーブル構成を正規化して改善した話を書いていきます。
はじめに
PR TIMESには各種メディアが登録されていて、プレスリリース配信をする際に配信先として選択できるメディアのリスト（以降メディアリストと呼びます）を作ることができます。メディアにはカテゴリが紐付いてメディアリスト作成画面のメディア検索機能で使われています。以下は実際のメディアリスト作成画面です。
左のサイドバーにあるカテゴリを選択してそのカテゴリに紐づくメディアを検索して、表示されたメディアをメディアリストに追加していくという画面になっています。
カテゴリの項目
メディアに紐付くカテゴリについて説明します。階層としては大・中・小があって、具体的には以下のようなカテゴリがあります。
| 大カテゴリ | 中カテゴリ | 小カテゴリ |
|---|---|---|
| メディア種別 | テレビ、雑誌、新聞など | 地上波、一般誌、専門紙など |
| エリア | 北海道・東北、関東、近畿など | 青森県、東京都、大阪など |
メディア種別は名前の通りメディアの種類で、エリアはメディアの報道対象地域という意味合いで使っています。
また、イメージしやすいように本ブログの冒頭で出しているメディアリスト作成画面のカテゴリ欄をピックアップしたものを貼っておきます。
メディアとカテゴリの紐付け方
次に、メディアとカテゴリがどのように紐付いているかをまとめました。⭕️がメディアと紐付いているという意味です。
| 中カテゴリ | 小カテゴリ | |
|---|---|---|
| メディア種別 | ⭕️ | |
| エリア | ⭕️ | ⭕️ |
エリアの紐付けに関して補足すると、メディアは各都道府県限定で配信する場合もあれば、全国向け配信としているメディアもあるため、中カテゴリと小カテゴリのそれぞれでメディアを紐づける必要があります。
カテゴリの改善内容
リニューアル前までは、メディアに紐付いているカテゴリは上記で書いたように2種類だけだったり、ちゃんとカテゴリに紐づいていないメディアなどありました。今回のリニューアルでメディアとの紐付けや既存カテゴリの分類の改善、新規カテゴリとして「業種」の追加をして「よりユーザが探しているメディアを表示できる」ようになりました。
以上を踏まえて、次に問題があったリニューアル前のテーブル構成を見ていきます。
リニューアル前のテーブル構成
以下に示しているER図が実際に問題があったカテゴリ周りのテーブル構成です。
上記のように全てのカテゴリを中カテゴリと小カテゴリそれぞれ一つのテーブルで管理していました 。また、メディアとカテゴリの紐付けも一つのテーブルで全て行っていました。上記のようなテーブル構成からカテゴリの改善について、どのような方針で進めるのかを見ていきます。
カテゴリ改善をどのように進めるか
今回のリニューアルでは既存のカテゴリデータに影響が出ないようにしてサービス自体にも影響が出ないように開発をしていきたかったです。よって、新しく全てのカテゴリについてテーブルを作りそこに新しいデータや既存のデータを入れていく方針にしていました。そこで、先ほど見た「非正規化のテーブル構成」で作るのか「正規化したテーブル構成」で作るのかですが、非正規化のテーブル構成だといくつか問題があったので正規化していくことにしました。
次に非正規のテーブル構成だと具体的にどのような問題があったかを書いていきます。
非正規化のテーブル構成での問題
主な問題は同じテーブルに複数のカテゴリが入っているので、本当は同じ構成ではないのに同じ「ような」構成で複数のカテゴリを一つのテーブルで扱おうとしていることが主な問題でした。
カテゴリの構成を変えたいときに他のカテゴリが影響受ける
もし、他のカテゴリが大カテゴリ・中カテゴリ・小カテゴリだけでなくさらに枠を増やしたい時に他のカテゴリと同じテーブルに入っているために、その構成を変えることが難しいです。例えば、エリアは日本だけの話をしていましたがグローバルでアジア・ヨーロッパがありその中に国があり、県がありとなった時に今のテーブル構成では対応できなくなります。
カテゴリごとにメディアとの紐付け方が違うのにそれが暗黙的になっている
メディアと小・中カテゴリの紐付けテーブルでは小カテゴリと中カテゴリの両方の紐付けを一つのテーブルでやっています。なので、テーブル構成でメディアとカテゴリの関係性を読み取るのが難しいです。また、エリアが中カテゴリと小カテゴリの両方を紐づけていることはアプリケーションコードに書かれているSQLを読んだりすればわかりますが、初めて・久しぶりに見る人には負担が大きいです。
カテゴリのデータを追加・削除する際に他のカテゴリが影響を受ける
今回はカテゴリのデータを改善していくにあたって、変えたい箇所と変えないでそのまま使う箇所もあったりしたので、削除したり追加したりする時にカテゴリ同士で影響しないかを考慮するのが大変になってしまいます。リニューアル前はメディア種別とエリアのみだったので、まだ良かったですがリニューアル後から業種というカテゴリも追加されるため、さらにカテゴリテーブルの関心が増えて管理がしにくくなっていきます。
仕様が不安定だった
これが正規化する大きい理由の一つでもありましたが、今回は「仕様が不安定」でありどのようなカテゴリが追加・削除されるか、どのカテゴリの階層（中カテゴリ・小カテゴリ）とメディアを紐づけるか決まらない状態が続いていました。全てのカテゴリについて同じ扱いができる可能性の方が低く、そのようなテーブル構成がよく変更されても影響範囲を最低限で変更しやすいテーブル構成にする必要がありました。
上記で挙げた問題は正規化したテーブル構成にすれば解決する問題なので、正規化したテーブル構成を新たに作成することにしました。次に実際に正規化したテーブル構成を見ていきます。
リニューアル後のテーブル設計について
上記のような問題があったため、カテゴリごとに微妙に違う仕様をそれぞれ独立させて管理できるように正規化されたテーブル構成にしていきます。実際のテーブル構成は以下のようにしました。
共通しているカテゴリの構成
カテゴリごとに以下のような構成で作っています。
- メディアとカテゴリの紐付けテーブル（中間テーブル）
- 中カテゴリのテーブル
- 小カテゴリのテーブル
メディアとカテゴリの紐付けテーブル
ここで、再度「業種」も追加したメディアとカテゴリの紐付け方の表を貼っておきます。
| 中カテゴリ | 小カテゴリ | |
|---|---|---|
| メディア種別 | ⭕️ | |
| 業種 | ⭕️ | |
| エリア | ⭕️ | ⭕️ |
メディア種別と業種は小カテゴリをメディアに紐付けるため、その中間テーブルを作成しています。エリアの場合は中カテゴリも小カテゴリもメディアとそれぞれ紐付けることができる仕様なので、中間テーブルを二つ作っています。これにより、メディアとカテゴリの紐付けが明示的になり分かりやすくなりました。
エリアの参照関係の循環
また、エリアのテーブル構成は参照関係が循環しています。理由としては、エリアは中カテゴリと小カテゴリそれぞれでメディアと紐づける必要があったからです。エリアと都道府県を親子関係ではなく兄弟関係にして一つのテーブルで管理すると、それぞれの紐付けを行うことが難しいため、このような構成にしています。
正規化して良かった話
テーブル作成・データ投入後に仕様の変更や追加がありましたが、正規化していたおかげでうまく対応できたので以下で紹介していきます。
業種テーブルの構成の変更
テーブル作成後に業種については「中カテゴリまでしか作れなくて小カテゴリを無くして、一旦はメディアと業種の中カテゴリを紐付けて欲しい」ということになりました。そのような要望でも今回のテーブル構成だと業種以外には何も影響がないし、小カテゴリと中カテゴリはテーブルとして分けているので、簡単に構成を変えることができました。以下のER図の通り「メディアと業種の小カテゴリの紐付けテーブル」を削除して、「メディアと業種の中カテゴリの紐付けテーブル」を追加するだけです。
エリアは日本だけでなく海外についても追加
PR TIMESに登録されているされているメディアの中には海外を報道対象地域としていることもあるのが分かり、「メディアのエリアが日本だけでなく海外もあったからデータを追加したい」という話が出てきています。また、今の時点では具体的な国までは紐付けないが、グローバル（全国と同じ立ち位置）としてメディアと紐付けはしたいということでした。これは今ある大カテゴリのエリアの意味合いが「日本」から「世界」に変わってきたり、中カテゴリ（関東、近畿など）と小カテゴリ（東京、大阪など）とはまた別の階層のカテゴリになりますので、テーブルの構成が変わっていくということです。
つまり、最初の方で話していた通り非正規化のままだとカテゴリの階層の追加が厳しくなっていたので、正規化していて良かったなと思いました。
正規化することで出てくる問題
正規化には上記のようなメリットがある一方で、正規化をしてテーブル数が増えることで参照時にデメリットが生じることもあります。今回はそのあたりをどのように考えたか次で書いていきます。
JOINの回数が増えてパフォーマンスが落ちる可能性がある
正規化をするとJOINが増えてパフォーマンスが落ちることがあります。今回で言うと、「メディアを検索する際に選択されたカテゴリに紐付くメディアを抽出する」が主な参照用途になります。参照時のパフォーマンス問題で大体の場合は、適切にINDEXが作成されていてデータを絞り込んでからJOINをすることでパフォーマンスの低下は防げます。
今回は「そこまでデータ数も多くない」かつ「データ数の増え方も緩やか」なので、パフォーマンスに大きく影響が出ることはないです。また、今回のカテゴリの紐付けを改善していくことによってカテゴリごとに紐づくメディアがより限定的になってデータ数を絞り込めるようになりました。
マテリアライズドビューについて
もしデータ量が増えてきてパフォーマンスが落ちてきたら、マテリアライズドビューを使う選択肢もあります。詳しくは触れませんが、今回のメディアとカテゴリの紐付けは更新頻度が低いため、パフォーマンス対策として有効な選択肢になります。
SQLが長くなり可読性が落ちる可能性がある
JOINが増えてSQLが長くなることで可読性が落ちることもあります。今回で言うと、メディアテーブルとJOINする各カテゴリとメディアの紐付けテーブル4つほどをJOINします。この対策として、ビューを使ったり先ほどのマテリアライズドビューを使うこともできますが、どちらも新しい管理コストがかかるので、今はそこまでしない方針にしています。
番外編: カテゴリ周りを処理するアプリケーションコード
最後に、正規化されたカテゴリ周りのテーブルを使ってアプリケーションコードを実装していて、思ったことがあったので書いていきます。
テーブルを正規化したことによるアプリケーションコードへの影響
今回テーブル構成を正規化してテーブルの数が増えました。今までは全てのカテゴリで共通で一意なIDで良かったですが、カテゴリごとにテーブルができて、IDもカテゴリごとに一意なものになりました。それにより、アプリケーションコードでバックエンドもフロントエンドもカテゴリごとに扱う必要が出てきて、コード量が増えました。これは元々が一緒に扱えていたことがあまり良くないので、関心が微妙に異なることもあり本来別々で扱うべきだと気づくことができました。
無理に共通化しようとしない
アプリケーションコードでも無理やり共通化するような処理を書くと、今回の非正規化のテーブルと同じように微妙に違う関心によって、開発がしにくい（運用しにくい）コードになってしまいます。例えば、今回はフロントエンドでは先ほど載せたメディア種別とエリアのように中カテゴリにはアコーディオンが設定されています。しかし、業種はそもそも中カテゴリしかないのでアコーディオンにできないということもありました。つまり、全てのカテゴリを共通の処理で書いているとその共通化した中にif文が増えていくことになりあまり良くないコードになってしまいます。
まとめ
機能的に参照に重点を置きたいのか・テーブル構成の管理に重点を置きたいのかを考えて、テーブル構成の管理（構成の変更や整合性の担保など）や参照の両方をバランスを見て設計することが大事だと実感でました。また、非正規化や正規化のテーブル構成は書籍とかではよく見ていましたが、実際に業務で非正規化から正規化するテーブル設計をするのは初めてだったので、無事に設計から運用までできて良かったです！
ここまで読んで頂きありがとうございます、少しでも参考になることがあれば幸いです。
