no-image

ハロプロ楽曲データベースの作り方3【列持ちテーブル解消編】

ハロプロ楽曲データベースシリーズ全記事見出し

複数アーティストにどのように対応するか

WEBアプリケーションを作成する際に、いきなりコーディングから始めるという人はいないと思います。まずはデータの入れ物となるデータベースの設計を先行するのがプログラミングのお決まりです。

そこで最初に何も考えずに設計したテーブルは以下のようなものでした。

ID楽曲名アーティスト作詞作曲編曲
000001ラララのピピピ道重さゆみつんくつんく大久保薫
000002エイティーン エモーションスマイレージつんくつんく板垣祐介
000003悲しきヘブン℃-uteつんくつんく板垣祐介
000004ロマンティック 浮かれモード藤本美貴つんくつんく上杉洋史

このテーブルを作った時に自分はこう思いました。勝ったwと… 「これならば楽勝でシステムの構築が出来そうだ。入力するのは大変かもしれないが、徐々に入れていけばなんとかなるだろう」と。しかしすぐに問題点に気が付きます。複数アーティストの問題です。

通常の歌手とは異なり、ハロープロジェクトは複数グループの集まりです。各グループが集まって歌う曲もあれば、グループ内の複数のアーティストが歌う曲もあります。例えば、好きだな君がは道重さゆみと譜久村聖2人によって歌われています。その問題を解決するために導き出されたテーブルが以下のようなものでした。

ID楽曲名アーティスト1アーティスト2作詞作曲編曲
000001ラララのピピピ道重さゆみNULLつんくつんく大久保薫
000002エイティーン エモーションスマイレージNULLつんくつんく板垣祐介
000003悲しきヘブン℃-uteNULLつんくつんく板垣祐介
000004ロマンティック 浮かれモード藤本美貴NULLつんくつんく上杉洋史
000005好きだな君が道重さゆみ譜久村聖つんくつんく大久保薫

すごく単純な話ですね。複数アーティストがいるのならば、その分アーティストの列を増やしてしまえば良いのです。ここではテーブルはアーティスト1、アーティスト2としており最大で2名までしか対応していませんが、もしものことを考え念のためアーティスト10まで列を作っておけば、最大で10人まで対応できるテーブルとなります。このようなテーブルを列持ちテーブルと言います。

しかし1曲あたりのアーティストの人数が最大で10名なんていう保障はどこにあるのでしょうか?もしも11人が参加する楽曲が出たら… その時はアーティスト11という列を作れば話は済むのでしょうか?ただ、テーブルの構造を変更するとアプリケーション側のロジックまで変更しなければならない事は当時の自分でも予測出来ました。マークアップのことを考えただけでもわかります。データ入力の際、アーティストを10名入れるtextboxを11個に増やす必要があるのです。このままだと、最大アーティスト数が増える度にコードを修正しなければならない羽目になります。

同じことが作詞や作曲にも言えます。共作で2人以上で作詞なり作曲する事は、ハロプロでも珍しくありません。アーティスト、作詞、作曲、編曲全ての関係者にそれぞれ10人分の列を用意していたら、とてつもなく列だらけのテーブルが出来上がってしまいます。加えて、ほとんどの楽曲は1人ずつしかアーティストと権利者がいない訳で、多くの行・列にNULLが入る事になり、さすがに初心者の自分も「何かがおかしい」という事に気が付きました。

行持ちテーブルと交差テーブル

行持ちテーブル

ではどのように複数アーティスト・複数権利者に対応させるかというと、行持ちテーブルを用います。すなわち、関連するデータを保持するのに列を増やすのではなく、その都度行に挿入するのです。具体的なテーブルは以下のとおりです。

楽曲テーブル
楽曲ID楽曲名アーティスト名
000001ラララのピピピ道重さゆみ
000005好きだな君が道重さゆみ
000005好きだな君が譜久村聖

複数アーティストがいる場合は、その分行として楽曲テーブルに挿入するわけです。今回は楽曲IDが000005の「好きだな君が」は2人アーティストがいるので2行挿入しています。仮に10人で歌う曲が出たとしても10行、20人の場合は20行挿入すれば良いので、理論的には最大アーティスト数は無限大となります。

第二正規化と交差テーブルの採用

再度、楽曲テーブルを掲載します。

楽曲テーブル
楽曲ID楽曲名アーティスト名
000001ラララのピピピ道重さゆみ
000005好きだな君が道重さゆみ
000005好きだな君が譜久村聖

このテーブルをよく見てみると、楽曲IDが000005だと曲は必ずラララのピピピになるという法則が存在します。このようにAという値を入れるとBが必ず出てくるという、プログラミングや数学の関数と同じ性質を関数従属といいます。

この関数従属は、通常は主キー列から非キー列に対して存在するものです。また、主キー列の一部から従属する列はあってはならないとされ、この関係を部分関数従属といいます。

今回のこのテーブルの場合、行を一意に特定するためには楽曲IDとアーティスト名を同時に主キーとしなければなりません。しかしながら、楽曲名は楽曲IDから導かれるので部分関数従属していると言えます。このような状態を解消するのが第二正規化です。

正規化の基本はテーブルの分割です。そしてテーブルの分割や新規作成の時に守るべき原則として、一つのテーブルは一つのエンティティ(実体)を表したものにしなければなりません。今回の場合ですと、楽曲というエンティティとアーティストというエンティティに分割できるでしょう。

楽曲テーブル
楽曲ID楽曲名
000001ラララのピピピ
000005好きだな君が
アーティストテーブル
アーティストIDアーティスト名
0001道重さゆみ
0002譜久村聖

その上で楽曲(テーブル)とアーティスト(テーブル)の情報を結びつけるために、交差テーブルを作成します。

楽曲アーティストテーブル(交差テーブル)
楽曲IDアーティストID
0000010001
0000050001
0000050002

一曲あたりのアーティストを増やすには、この交差テーブルに楽曲IDとアーティストIDを挿入するだけです。またこのテーブルの構造ですと、とりあえず曲だけ登録したいとか、逆にアーティストだけ先に登録して楽曲は後で登録するということも可能になります。

ここまでのテーブルの構造をER図で示すと、以下の様になります。なおこのER図は、ER Masterで作成しています。

第一正規化

ここで少し話がそれるのですが、もしかしたら複数アーティスト・権利者に対応するために次のようなテーブルを思いついた方もいるのではないでしょうか?

ID楽曲名アーティスト作詞作曲編曲
000001ラララのピピピ道重さゆみつんくつんく大久保薫
000005好きだな君が道重さゆみ,譜久村聖つんくつんく大久保薫

アーティスト列を一列にする代わりに、一つのセル(スプレッドシートで使う用語でデータベースの正式用語ではありませんが、便宜的にこのように表記します)にカンマ区切りなどで2人以上のアーティストを入れてしまうのです。この方法でも、行持ちテーブルと同じく無限大にデータを保持することが出来るように思えます。

しかしこのようなテーブル設計は第一正規形に反した設計です。第一正規形とは、セルには単一の値を入れなければならないという原則です。000005の「好きだな君が」のアーティストは、道重さゆみと譜久村聖という2つの値が入っているので、要件を満たしていないという事になります。

なぜこのようなテーブル設計がいけないのでしょうか?そもそもなぜ正規化をする必要があるのでしょうか?それはデータの不整合を防ぐためにあるのです。例えば次のようなテーブルがあったとしましょう。

ID楽曲名アーティスト作詞作曲編曲
000001ラララのピピピ道重さゆみつんくつんく大久保薫
000005好きだな君が道重さゆみ,譜久村聖つんくつんく大久保薫
000006大きい瞳亀井絵里,道重さゆみ,田中麗奈つんくつんく平田祥一郎

初めと同じように、複数アーティストに対してはコンマ区切りでデータを挿入しています。しかしこのテーブルのデータには誤りがあり、000006の大きい瞳は亀井絵里,道重さゆみ,田中れいなの3人で歌われている曲であり、田中麗奈(女優)は楽曲には参加していません。

なぜこのようなミスが発生してしまったのでしょうか?おそらく入力したアプリケーションの仕様として、アーティスト名を入力するテキストボックスが存在し、ユーザーが手入力でカンマ区切りしながら1つずつ入力するというようなアプリだったのでしょう。その際に「たなかれいな」と入力し変換するところを「たなかれな」と入力し変換してしまったのだと思われます。

こういったミスを防ぐためには、事前にアーティストテーブルに登録されたアーティストしか登録されないようすれば良いのです。アプリケーション側の実装としては、ドロップダウンリストからアーティストを選択し、+ボタンなどでアーティストを追加していくようなGUIになるでしょう。

またデータベースには参照整合性制約を設定することができますので、事前に登録されたアーティストID以外受け付けなくする事が出来ます。これがコンマ区切りの文字列で入力してしまうと、そのような制約を課す事は出来ません。

この他にも、コンマ区切りに入力していっても文字列長は無限ではありませんから、やはり限界が来る可能性もありますし、何よりもデータを変更する時が大変です。先ほどの例ですと、「亀井絵里,道重さゆみ,田中麗奈」を修正するのに、田中麗奈の一番後ろにカーソルをあわせDelを4回押し「田中れいな」と入力するのでしょうが、間違えてDelを3回しか押さなかったために「田田中れいな」さんが挿入されないとも限りません。また将来コンマを含んだアーティストを入力する可能性も否定出来ません。

テーブルの設計は常に正規化を意識しながら行わなければならないのです。

group_concat関数でコンマ区切りのビューを実現する

ではここで実際にテーブルを結合し、目的のデータが得られるかを実験してみたいと思います。


SELECT 楽曲テーブル.楽曲ID,楽曲テーブル.楽曲名,アーティストテーブル.アーティスト名
FROM 楽曲テーブル
INNER JOIN 楽曲アーティストテーブル ON 楽曲アーティストテーブル.楽曲ID = 楽曲テーブル.楽曲ID
INNER JOIN アーティストテーブル ON アーティストテーブル.アーティストID = 楽曲アーティストテーブル.アーティストID

結果は以下のようになります。

楽曲ID楽曲名アーティスト名
000001ラララのピピピ道重さゆみ
000005好きだな君が道重さゆみ
000005好きだな君が譜久村聖

ここで問題となるのは、アーティストが複数いる場合は、その分だけ行が結果として返ってくるという事です。もちろん、このような挙動でも問題ないケースもあるのかもしれませんが、1曲につき1行の結果を返すのが感覚的には正しいでしょう。

一つの解として、楽曲名を取得するSQLとアーティストを取得するSQLとを分け、二回SQLを発行するということも考えられます。


SELECT 楽曲テーブル.楽曲ID,楽曲テーブル.楽曲名
FROM 楽曲テーブル
WHERE 楽曲テーブル.楽曲ID = '000005'

SELECT アーティストテーブル.アーティスト名
FROM 楽曲アーティストテーブル
INNER JOIN アーティストテーブル ON アーティストテーブル.アーティストID = 楽曲アーティストテーブル.アーティストID
WHERE 楽曲アーティストテーブル.楽曲ID = '000005'

アーティストテーブルからは複数行が返ってきますが、foreach構文などでイテレートして表示すればよいでしょう。しかし場合によっては、どうしてもSQL側で1行に整形して出力したいという要望もあるでしょう。その場合、MySQLにはgroup_concat関数がありますのでそれを利用します。


SELECT 楽曲テーブル.楽曲ID,楽曲テーブル.楽曲名,GROUP_CONCAT(アーティストテーブル.アーティスト名) AS アーティスト名
FROM 楽曲テーブル
INNER JOIN 楽曲アーティストテーブル ON 楽曲アーティストテーブル.楽曲ID = 楽曲テーブル.楽曲ID
INNER JOIN アーティストテーブル ON アーティストテーブル.アーティストID = 楽曲アーティストテーブル.アーティストID
GROUP BY 楽曲テーブル.楽曲ID,楽曲テーブル.楽曲名
楽曲ID楽曲名アーティスト名
000001ラララのピピピ道重さゆみ
000005好きだな君が道重さゆみ,譜久村聖

複数の行がカンマ区切りで、一つの行にまとめられているのがわかります。