はじめに
Oracle、MySQL、PostgreSQLといったリレーショナルデータベースシステム(RDBMS)は、
さまざまなシステムにおいて必要不可欠な要素のひとつです。
データベースはDBエンジニアという専門職が存在し（DBエンジニアの中でもさらに細分化されることもある）、
それだけにDBを知らないエンジニアにとっては敷居の高いものと感じられることがあります。
しかし、特に小さな職場やチームにおいてはデータベースのプロフェッショナルが存在せず、
DBをよく知らないSEやプログラマがDBエンジニアの役割を代替するといったケースもあります。
1からデータベースの技術をしっかり習得できればそれに越したことはありませんが、
データベースにクエリを投げるAPIも作って、さらにはそれを呼び出すクライアントプログラムも作って、
などとやっているとなかなかデータベースにフォーカスを当てて注力できないというケースもあるでしょう。
そういった立場の人たちに対して、
「とりあえずこれだけ知っていればDB設計できるよ！」
という基本的な考え方を、なるべくシンプルにここではお伝えできればと思います。
リレーショナルデータベース とは
大層な名前がついていますが、とどのつまりリレーショナルデータベースとは、表の集まりです。
つまり、エクセルに作成するような表がいくつか集まって構成されているもの、です。
「データベースを設計する」というと身構えてしまいますが、
「エクセルの表を作る」と考えると敷居が随分下がるのではないでしょうか。
設計してみる
なにはともあれ、設計してみましょう。
といっても、やることはエクセルのような表を作るだけです。
例として、日々の食事の記録のようなものを考えてみましょう。
健康のために日々の食事ログをエクセルにまとめる、といった感じです。
| 日時 | 食事種別 | 料理 | カロリー(kcal) | 塩分(g) | 料理種別 | 料理種別補足 |
|---|---|---|---|---|---|---|
| 2019/01/01 | 朝食 | ご飯 | 240 | 0 | 主食 | 主に炭水化物とエネルギーになるもの |
| 2019/01/01 | 朝食 | 卵焼き | 151 | 0.9 | 主菜 | 主にタンパク質になるもの |
| 2019/01/01 | 朝食 | 味噌汁 | 35 | 1.5 | 汁物 | 食事と一緒に摂取するスープ類 |
| 2019/01/01 | 昼食 | たまごサンド | 220 | 2.5 | 主食 | 主に炭水化物とエネルギーになるもの |
| 2019/01/01 | 昼食 | コーヒー | 30 | 0.3 | 飲み物 | 食事と一緒に摂取する飲料 |
| 2019/01/01 | 夕食 | ご飯 | 240 | 0 | 主食 | 主に炭水化物とエネルギーになるもの |
| 2019/01/01 | 夕食 | ビール | 60 | 0.5 | 飲み物 | 食事と一緒に摂取する飲料 |
完成しました。
これをSQLにしてデータベースシステムに投入すれば、データベースの完成です。
ちなみに一般的に「設計」というと
| 日時 | 食事種別 | 料理 | カロリー(kcal) | 塩分(g) | 料理種別 | 料理種別補足 |
|---|
ここを作ることを指します。
なので、設計としてはこれで終わりです。
終了。
…では、ありませんね。
設計を見直してみる
プログラムを書いたことがある方なら、このデータの扱いにくさに気づくことでしょう。
- 値が重複していることによるデメリット
- データ量が多くなる
- 重複している値の整合性をチェックする必要がある
- たとえば「卵焼き」が1行のみ「タマゴ焼き」になっていたらデータを正常に処理できない
- 卵焼きのカロリーが実は160kcalだったことがわかったら、卵焼きの全行を更新する必要がある
などなど、いろいろとめんどくさそうですね。
なぜ重複が起こるのかというと、それぞれの値の「多重度」が異なるからです。
たとえば1種類の「料理種別」に対して「料理」は複数あります。
これをそのまま表にしてしまうと、料理の数分料理種別とその補足情報が重複します。
また、1回の食事に対して、複数の料理があります。
これをそのまま表にしてしまうと、食事の数分料理が重複します。
逆に、1つの料理に対して、複数の食事があるところもあります。
（「ご飯」は朝食と夕食で重複している）
重複しないようにするにはこれらを別のテーブルに分ける必要があります。
食事テーブル
| 日時 | 食事種別 |
|---|---|
| 2019/01/01 | 朝食 |
| 2019/01/01 | 昼食 |
| 2019/01/01 | 夕食 |
料理テーブル
| 料理 | カロリー(kcal) | 塩分(g) |
|---|---|---|
| ご飯 | 240 | 0 |
| 卵焼き | 151 | 0.9 |
| 味噌汁 | 35 | 1.5 |
| たまごサンド | 220 | 2.5 |
| コーヒー | 30 | 0.3 |
| ビール | 60 | 0.5 |
料理種別テーブル
| 料理種別 | 料理種別補足 |
|---|---|
| 主食 | 主に炭水化物とエネルギーになるもの |
| 主菜 | 主にタンパク質になるもの |
| 汁物 | 食事と一緒に摂取するスープ類 |
| 飲み物 | 食事と一緒に摂取する飲料 |
3つのテーブルに分割できました。
情報の重複がなくなりましたね。
これで、卵焼きのカロリーが変わっても1箇所の変更で済みますし、卵焼きとタマゴ焼きの表記揺れも起こらなくなります。
設計部分を抜き出すと以下のようになります。
食事テーブル
| 日時 | 食事種別 |
|---|
料理テーブル
| 料理 | カロリー(kcal) | 塩分(g) |
|---|
料理種別テーブル
| 料理種別 | 料理種別補足 |
|---|
終了。
…では、ありませんね。
これでは、どの食事にどの料理を食べたのか、また、料理の種別はなんなのか、わかりません。
設計をさらに見直してみる
ここで、リレーショナルデータベースのリレーショナルデータベースらしい設計をすることで解決できます。
リレーショナル、というのは日本語で「関係」です。
つまり、着目すべきはテーブル同士の関係になります。
関係っていってもなにを見るの？ という疑問がわきますが、
リレーショナルデータベースの設計における関係としてまずみるべきはそれぞれの「多重度」になります。
まず、料理テーブルと料理種別テーブルの関係を見ていきます。
多重度を見る際は、**「片方のテーブル1件に対してもう片方のテーブルが何件存在し得るか」**を考えます。
料理テーブル1件、つまり1つの料理に対して、料理種別は1つですので、「必ず1件」となります。
逆に、1つの料理種別に対して料理は「複数存在」します。
（飲み物という種別に対してコーヒー、ビール、など）
また、種別だけが定義されて料理がない可能性もあります。
（たとえばここに「デザート」という種別を追加した場合、それに紐づく料理はありません）
ですので、「0～n件」となります。
図にすると以下のようになります。
リレーショナルデータベースの設計図としてよく使われる「ER図」です。
1:0..nなどと書かれているとイメージしづらいですが、要するに先述の通り
「片方のテーブルのレコード1件に対してもう片方のテーブルのレコードが何件存在し得るか」
を考えればよいだけです。
この図から、以下のような設計ができます。
料理テーブル
| ID | 料理 | カロリー(kcal) | 塩分(g) | 料理種別ID |
|---|---|---|---|---|
| 1 | ご飯 | 240 | 0 | 1 |
| 2 | 卵焼き | 151 | 0.9 | 2 |
| 3 | 味噌汁 | 35 | 1.5 | 3 |
| 4 | たまごサンド | 220 | 2.5 | 1 |
| 5 | コーヒー | 30 | 0.3 | 4 |
| 6 | ビール | 60 | 0.5 | 4 |
料理種別テーブル
| ID | 料理種別 | 料理種別補足 |
|---|---|---|
| 1 | 主食 | 主に炭水化物とエネルギーになるもの |
| 2 | 主菜 | 主にタンパク質になるもの |
| 3 | 汁物 | 食事と一緒に摂取するスープ類 |
| 4 | 飲み物 | 食事と一緒に摂取する飲料 |
それぞれのテーブルのレコードを一意に特定するためにID列を追加しました。
IDはユニークな値であればなんでもかまいませんが、ここでは数値の連番で振っています。
このような値を**プライマリキー(PK)**と呼びます。
この値を指定することで任意のレコードを特定できるから、そのレコードのキー（鍵）なのです。
SQLで書いてみます。
◆卵焼き」を取得
SELECT *
FROM 料理テーブル
WHERE ID = 2
結果
| ID | 料理 | カロリー(kcal) | 塩分(g) | 料理種別ID |
|---|---|---|---|---|
| 2 | 卵焼き | 151 | 0.9 | 2 |
料理テーブル側のレコードが1件に絞れたら、料理種別側も絞れます。
ですので、絞る側（料理テーブル）に絞られる側（料理種別テーブル）のIDを格納します。
これで、料理が絞れれば料理種別も絞れることになるのです。
このように、関連先のテーブルを一意に絞れるキーを**外部キー(FK)**と呼びます。
SQLで書いてみます。
◆「卵焼き」とその種別を取得
SELECT 料理テーブル.料理, 料理種別テーブル.料理種別, 料理種別テーブル.料理種別補足
FROM 料理テーブル
JOIN 料理種別テーブル
ON (料理テーブル.料理種別ID = 料理種別テーブル.ID)
WHERE 料理テーブル.ID = 2
結果
| ID | 料理 | 料理種別 | 料理種別補足 |
|---|---|---|---|
| 2 | 卵焼き | 主菜 | 主にタンパク質になるもの |
ID2の卵焼きレコードの外部キーの値を使って、料理種別テーブルと結合できていることがわかります。
同様に、食事テーブルと料理テーブルの関係をみてみます。
1回の食事に対して1つ以上の複数の料理が紐付きます。
1/1の朝食に対してご飯、卵焼き、味噌汁の3品が紐付いています。
食事で何も食べないということはないので、最低1です。
つまり「1～n件」となります。
逆に、1つの料理に対して複数の食事が紐付きます。
ご飯を朝食と夕食で食べているので、この場合食事側は2件となります。
料理を定義して食事に出さない、ということも設計上は可能ですので、
ここは「0～n件」となります。
ER図にすると以下のようになります。
今度はどちらのテーブルもnなので、先程のようにどちらかのテーブルに外部キーを作成して紐付けることができません。
（相手のレコードが一意に特定できないため）
このように関係がn:nになった場合の設計のセオリーがあります。
真ん中に一つ、関連テーブルを挟むことです。
ここは感覚的にはわかりにくい部分ですが、
「n:nの関連だったら間に関連テーブルを挟む」
と機械的に覚えてしまって問題ないでしょう。
食事テーブル
| ID | 日時 | 食事種別 |
|---|---|---|
| 1 | 2019/01/01 | 朝食 |
| 2 | 2019/01/01 | 昼食 |
| 3 | 2019/01/01 | 夕食 |
食事で食べた料理テーブル
| ID | 食事ID | 料理ID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 5 |
| 6 | 3 | 1 |
| 7 | 3 | 6 |
料理テーブル
| ID | 料理 | カロリー(kcal) | 塩分(g) | 料理種別ID |
|---|---|---|---|---|
| 1 | ご飯 | 240 | 0 | 1 |
| 2 | 卵焼き | 151 | 0.9 | 2 |
| 3 | 味噌汁 | 35 | 1.5 | 3 |
| 4 | たまごサンド | 220 | 2.5 | 1 |
| 5 | コーヒー | 30 | 0.3 | 4 |
| 6 | ビール | 60 | 0.5 | 4 |
SQLで書きます。
◆昼食で食べた料理
SELECT 料理テーブル.料理
FROM 食事で食べた料理テーブル
JOIN 料理テーブル
ON (食事で食べた料理テーブル.料理ID = 料理テーブル.ID)
WHERE 食事で食べた料理テーブル.食事ID = 2
結果
| 料理 |
|---|
| 卵焼き |
| コーヒー |
◆ご飯を食べた食事
SELECT 食事で食べた料理テーブル.日時, 食事で食べた料理テーブル.食事種別
FROM 食事で食べた料理テーブル
JOIN 食事テーブル
ON (食事で食べた料理テーブル.食事ID = 食事テーブル.ID)
WHERE 食事で食べた料理テーブル.料理ID = 1
結果
| 日時 | 食事種別 |
|---|---|
| 2019/01/01 | 朝食 |
| 2019/01/01 | 夕食 |
このように、真ん中のテーブルから検索することで、左右のテーブルのレコードを特定することができます。
設計結果
ER図は以下。
テーブル定義は以下。
食事テーブル
| ID | 日時 | 食事種別 |
|---|
食事で食べた料理テーブル
| ID | 食事ID | 料理ID |
|---|
料理テーブル
| ID | 料理 | カロリー(kcal) | 塩分(g) | 料理種別ID |
|---|
料理種別テーブル
| ID | 料理種別 | 料理種別補足 |
|---|
今回はイメージしやすくするためカラムの定義からER図にしましたが、
本来の設計アプローチとしてはまずはER図を作成することにあります。
（実際に図にしなくても、イメージできているか）
ER図ができていれば、設計はできたも同然です。
関連に従ってキーを設定し、紐付ければいいだけですから。
主キー(PK)、外部キー(FK)が設定できていれば、
あとは各テーブルに必要な属性を追加していけばいいだけです。
料理テーブルであればたとえば他の栄養素（タンパク質など）の列を追加していってもいいでしょう。
繰り返しますが、重要なのはあくまで**「関連」**です。
料理テーブルに前述のような列を追加していっても、全体の関連の設計には影響しないことがわかります。
以下のようになっても、ER図には影響しません。
料理テーブル
| ID | 料理 | カロリー(kcal) | 塩分(g) | 炭水化物 | タンパク質 | 鉄分 | 料理種別ID |
|---|
さいごに
今回の例はとてもシンプルなモデルなのであまり迷う余地はありませんでしたが、
現実にさまざまなデータを設計しようとすると、その構造は複雑です。
どこがどう関連しているのかわかりにくいデータもあると思います。
また、膨大なER図の関連によって結びついている巨大な設計もあるでしょう。
しかし、ベースとなる考え方は今回の内容を適用できます。
同じ対象でも、システムの要件によって最適な設計が変わることもあります。
たとえば今回の設計では食事テーブルに朝食、昼食、夕食と入っていますが、
「食事」と「食事種別（朝食、昼食、夕食、間食など）」は
実はn:1の関係になっています。
（食事側がn）
しかし、テーブルに必要な情報がこれだけなら、この2つを別テーブルにするというのは
現実的な解ではないでしょう。
重複を排除していくということは、それだけテーブル数が増え、クエリも複雑化するということでもあります。
これらはトレードオフです。
サンプルのSQLではJOIN句を使って結合して抽出したりしていますが、
一番最初の状態（1テーブルにすべて詰め込んだ状態）であれば、JOINは必要ないのです。
たとえば、
「食事種別によってカロリーの重み付けを行って（夕食は脂肪になりやすい、など）
最適なカロリー摂取量になっているか管理するシステム」
のようなものであれば、食事種別に重み情報などが付加されることが想像できます。
この場合、食事種別テーブルは別に分けたほうがよいということになります。
このように、最適解の境界線はとてもファジーなものです。
それでも「なんとなくテーブルを分けた／分けなかった」よりも、
「こういう分け方ができるが、今回はこういう理由でテーブルを分けた／分けなかった」
ときちんと理由付けがされているほうが、最適解に近づけることは事実です。
この内容がよりよい設計の一助となれば幸いです。