no-image

ハロプロ楽曲データベースの作り方4【テーブルの継承】

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

権利者テーブルの作成

前回の記事で、どのように複数アーティストに対応するかを見てきました。しかし複数人に対応しなければならないのは、アーティストだけではなく作詞家などの権利者(著作者)も同様です。早速、複数権利者に対応したテーブルを設計してみましょう。

まずは前回からの、アーティストを格納するテーブル群を掲載したいと思います。

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

今回から新たに権利者を格納するテーブルを作成します。まずは作詞や作曲を担当する権利者のエンティティである権利者テーブルの作成からです。

権利者テーブル
権利者ID権利者名
0001つんく
0002大久保薫

アーティストとは異なり、権利者には作詞や作曲などの区別が必要です。そのために権利者種別テーブルを作成します。

権利者種別テーブル
権利者種別ID権利者種別名
01作詞
02作曲
03編曲
04日本語訳詞
05ストリングスアレンジ

最後に、楽曲ごとの権利者と権利者種別を格納する交差テーブル、楽曲権利者テーブルを作成します。

楽曲権利者テーブル(交差テーブル)
楽曲ID権利者ID権利者種別ID
000001000101
000001000102
000001000203
000005000101
000005000102
000005000203

ER図にすると以下のようになります。

本番で使われているデータベースには、商品を格納するテーブルやYouTubeの情報を格納するテーブルなどが存在しますが、楽曲データベースを運用する上でのコアな構造は以上のテーブルで全てです。また、読者の方が実際に楽曲データベースの運用をお考えでしたら、上記のテーブル構造でも全く問題なく運用できると思います。

テーブルの継承

冗長なデータに対処する

開発直後は上記のテーブルで運用していたのですが、しばらく経った頃ある事実に直面します。アーティストでありかつ、作詞や作曲も手掛ける人物が存在するのです。例として中島卓偉さんが挙げられますが、同じ人物の情報を2つのテーブルに挿入するのは、なんとなくおかしい感じがします。

もちろん一つの対処法というか、これを問題とはみなさずに、そのまま普通に両方入れてしまうという解もあるでしょう。テーブルの正規化という観点から見ても全く問題のない方法です。

しかし2つのテーブルに同じデータが入っているのは冗長で、やはりどこかおかしい感じがします。また今回のこの記事では省略していますが、アーティストや権利者を格納するテーブルには、読み仮名と英語表記を格納する列が実運用のデータベースには作成されており、当然そういったデータも二度入れる事になります。データを入力する作業が増えるという事は、データ入力時にミスをする確率も同時に高めます。これにより、片方のテーブルともう片方のテーブルとの間でデータの齟齬をきたす可能性が考えられます。

アーティストテーブル
アーティストIDアーティスト名カナ
0001道重さゆみミチシゲサユミ
0002譜久村聖フクムラミズキ
0003中島卓偉ナカジマタクイ
権利者テーブル
権利者ID権利者名カナ
0001つんくツンク
0002大久保薫オオクボカオル
0003中島卓偉ナカシマタクイ

上記テーブルのように中島卓偉さんの読みを、片方のテーブルには「ナカジマタクイ」と正しく入力しているのに、もう片方には「ナカシマタクイ」と入力してしまい、データの齟齬が発生しています。こういった事態を防ぐにはどうすれば良いのでしょうか。

アーティストと権利者をまとめる

そこですぐに思いつきそうな答えとして、アーティストと権利者を一つのテーブルにまとめて、「人物テーブル」を作成するという方法があるでしょう。

人物テーブル
人物ID人物名
0001道重さゆみ
0002譜久村聖
0003中島卓偉
0004つんく
0005大久保薫

主キー列に重複した値は入れられないので、IDは一意になるように重複が無いよう変更しています(IDを途中で変更すると、交差テーブルのIDも変更する必要があり実際に変更作業が発生したのですが、今回の記事では割愛します)。ER図にすると以下のようになります。

er2

このテーブルでも実際の運用には何ら問題はありません。しかし、アプリケーション側で実装するときに少し厄介な問題に直面します。ある楽曲のアーティストを入力しようとドロップダウンリストから選択する際に、アーティストを選択するリストに権利者までもが表示されてしまうのです(大久保薫さんはアーティストではないのにもかかわらず、アーティスト一覧に表示されてしまうのです)。

言語やフレームワークに依存するので細かくは解説しませんが、アーティストを選択するドロップダウンリストはテーブルから全てのデータを取得し、イテレートして表示するような形式になるでしょう。先程まではアーティストと権利者が別々のテーブルになっていたので、アーティストを選択するドロップダウンリストにはアーティストしか表示されませんでした。権利者も同様です。しかし今回からアーティストと権利者が一緒になったために、全ての人物が表示されるようになってしまいました。

アーティストと権利者を区別するフラグを立てる

この問題に対処する一つの方法として、人物テーブルにアーティストかどうか、権利者かどうかの情報を格納する列を作成する方法が考えられます。すると以下のようになります。

人物テーブル
人物ID人物名アーティストフラグ権利者フラグ
0001道重さゆみ10
0002譜久村聖10
0003中島卓偉11
0004つんく01
0005大久保薫01

1がtrueで0がfalseを表します。MySQLにはboolean型は存在しませんので、TinyInt型などを採用すればよいでしょう。もしもアーティストを入力するドロップダウンリストを作成する場合は、以下のようなクエリで情報を取得する事になります。


SELECT 人物テーブル.人物ID,人物テーブル.人物名
FROM 人物テーブル
WHERE 人物テーブル.アーティストフラグ = 1

再びER図にしてみましょう。

er2

参照整合性制約を維持出来ない

これでドロップダウンリストに余計な情報が表示される問題は解決されました。しかしこの方法ではまだ、潜在的な問題が潜んでいます。それはテーブルに参照整合性制約を課すことが出来ないのです。

最初に設計した方法では、アーティストテーブルと権利者テーブルが別れており、なおかつ楽曲アーティストテーブル(交差テーブル)には外部キー制約が課されていたので、楽曲アーティストテーブルに作詞家などの権利者が入り込む余地はありませんでした。楽曲権利者テーブルにしても同様です。

しかし今回のテーブルの構造では、アーティストと権利者が「人物テーブル」にひとまとめになっているので、楽曲アーティストテーブルに権利者が入り込む事は理論上可能です。ただしアプリケーション側でしっかりと実装し、開発後は開発したフロントエンドのGUIに従いデータを入力しさえすれば、不整合なデータを入力することはまず無さそうです(ドロップダウンリストから選択するようにすれば、間違ったデータが入力されることはありません)。もちろん、それをもって問題解決としても良いでしょう。しかし何らかの事情で、データベースエンジニアやデータベースアドミニストレーターがSQL文を発行し、CUI上で手入力する可能性も捨て切れませんし、ミドルウェア側(DB側)でエラーを弾く仕組みは欲しい所です。

クラステーブル継承

ここはやはりアーティストと権利者のテーブルを分けるべきでしょう。しかし2つのテーブルに共通する属性に関しては一つにまとめ、データの冗長性も排除したい所です。そこで用いる手法がクラステーブル継承という手法です。

これは簡単に説明すると、リレーショナル・データベースのテーブルをオブジェクト指向プログラミングのクラスに見立て、親クラスならぬ親テーブルとそれを継承する子テーブルを作成します。そして共通の性質(属性)に関しては親テーブルに格納し、各個別の属性に関しては子テーブルに格納するのです。言葉だけでは説明しづらいので、まずはER図を示したいと思います。

er2

まず基底テーブル(基底クラス)として人物テーブルがあり、人物テーブルには人物名という属性(プロパティ)があります。この基底テーブルを継承する子テーブル(子クラス)としてアーティストテーブルと権利者テーブルが存在します。今回の要件では、小テーブルに固有の属性はありませんので、主キーのIDのみが格納される事になります。

ここでまとめとして全体のER図を示したいと思います。

er2

このテーブルでは例えばアーティストを登録する場合は、人物テーブルに登録した後そのIDをアーティストテーブルにも挿入します。権利者も同様です。アーティストと権利者を両方兼ねている場合は、三つのテーブルに挿入します。挿入の作業が複数回発生しますが、参照整合性制約を課すことが出来ますし、先のドロップダウンリストの問題も解決します。例としてアーティスト一覧を取得するクエリを示します。


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

余談ですが、もしも誕生日という属性を格納したい場合、アーティストも権利者も誕生日の情報が必要だという場合は、親テーブルに誕生日列を設ける事になるでしょう。

er2

しかし、誕生日の情報はアーティストだけで十分というケースも考えられます。その場合、誕生日という属性はアーティストのみに必要ですので、アーティストテーブルに列を追加します。

er2