Uzabase Tech Blog

SPEEDA, NewsPicks, FORCASなどを開発するユーザベースの技術チームブログです。

方法より原理 〜正規化ルールとリレーショナルモデルについて〜 【実践編】

今日は。
SPEEDA を開発している濱口です。

理屈編では、まずリレーショナルデータベース(以下、RDB)の論理設計やその後において、
正規化ルールを運用する難しさについて述べました。

主な要因として、
例えば正規化を一度完了したテーブルに対し SELECT した結果もまたテーブル*1
つまりは正規形であることが求められるため、 SELECT するたびにいちいちその結果について、
「第 n 正規化についてクリアだな…次に第 n + 1 正規化についてはどうかな…」などと、
ルールに当てはめてチェックしなければならないことを挙げました。

リレーショナルモデルの世界で、常に正しい処理結果を得るため、
リレーショナル演算の閉包性を維持しなければならないためです。

そこで、ルールに頼らなくてもそれが依って立つリレーショナルモデルさえ正しく理解していれば常に、自然に正規形がつくれるのではないか、という仮説のもとモデルの説明をしました。

今回は、実際に設計を行いながら、それを確かめてみることにします。
正規化ルールを一旦忘れて取り組んでみます。

こんなデータをもらったら…

よくある飲食店のレシートです。

f:id:yhamaro:20200314083602p:plain
とある日

店主から依頼され、この取引を記録したいとしましょう。
この取引があったという事実を私が責任を持って永続化するのです。

とりあえず

レシートにある情報をもらさず、ひとつの取引事実としてRDBに格納しました。
(「合計」のような導出データは入れません。)

番号 日付 品目 種別 単価(円) 数量 店舗
1 2020/02/29 ラーメン, 餃子, ビール 麺飯類, 冷菜・点心, ドリンク 650, 450, 630 1, 2, 3 西東京_久我山店

ところで、店主が「月末の集計が楽しみだな。なにせウチは西東京でトップの売上らしいから…。」と言っているのを耳にしました。
ここ久我山店は「西東京」というエリアに属するらしいので、店舗名にその情報も加えておきました。

また、このチェーン店らしきお店で使っているレジは全店舗に設置されており、
レシートの番号は日毎に全店共通の連番として払い出されていることもわかりましたので、
日付と番号で一意キーを設定しておきます。(属性の下に二重線で表しています)

これでいいのだろうか…

今時点で、このレシート情報を記録、蓄積したものをどのように使うのかはよくわかっていません
誰かが(私かもしれない)がクエリでアドホックに分析を行うのか、これをデータストアとしたWebアプリケーションが後々できるのか…。

でも、そんなことを気にする必要はありません
リレーションに登録するのは事実であり、今時点で捉えたい事実を正しく登録できるよう設計すればよいのです。
分析要件やアプリケーションの仕様によって、リレーションに登録された事実が変わることは無いからです。
そういう意味では、RDBの論理設計はアプリケーション設計から独立していると言えるでしょう。

なので、今は自信と確信を持って事実をより正しくリレーションにマッピングすることに集中しましょう!

f:id:yhamaro:20200315081429p:plain
コックタイのほうを優先しました

最初に気になること

リレーションに登録するデータは命題の集合であることを前回述べました。
ここで、一部の属性を取り出して以下の述語を考えてみます。

(種別) として、 (品目) を注文しました。

これに登録したデータを当てはめて命題にしてみます。

麺飯類, 冷菜・点心, ドリンク として、 ラーメン, 餃子, ビール を注文しました。

この命題は事実を正しく表現できているでしょうか。
なにを言っているのか、わかりませんね。
いやわかるよ、という人も頭の中で以下の命題に分解しているはずです。

麺飯類 として、 ラーメン を注文しました。
冷菜・点心 として、 餃子 を注文しました。
ドリンク として として、 ビール を注文しました。

使用には、用法を守って正しく

前回、リレーションに格納された命題に順序が無いことを述べましたが、 リレーショナルモデルでは順序の概念を意図的に排除しています。
上記の設計のように属性の値に順序の概念を持ち込むとしたら、それを扱える演算を一緒に用意しなければなりません。

例えば、品目「ラーメン」の種別が知りたい場合、該当のレコードを取り出し、品目と種別の値をカンマで分割し、順序をまもってマッピングした後、「麺飯類」にたどり着けます。
そんな演算を実装できたとしても、少なくともリレーショナルモデルには持ち込みたくないですね。

なので、同じことをリレーショナル演算の一部である射影( SQL の SELECT ) と制限( SQL の WHERE ) で出来る設計に変えます。

番号 日付 品目 種別 単価(円) 数量 地域名 店舗名
1 2020/02/29 ラーメン 麺飯類 650 1 西東京 久我山店
1 2020/02/29 餃子 冷菜・点心 450 2 西東京 久我山店
1 2020/02/29 ビール ドリンク 630 3 西東京 久我山店
SELECT 種別 FROM 取引 WHERE 品目 = 'ラーメン';
-> '麺飯類'

できました。

わかったこと

上記の例は順序という概念で分割できるものでしたが、意味的に単一で無いものを属性の値として格納するのは(それを扱える演算をリレーショナルモデルに追加できないかぎり)避けるべきことがわかりました。
秒で反省して、「店舗」属性も、「地域名」と「店舗名」に分割しています。

ところで、ここで行った作業によって設計がリレーショナルモデルにとってふつうのカタチになったので、この作業を指して Normalization と呼び習わすことにします。

次に気になること

次の客が来て、ラーメンだけ食べて帰ったのでそれを記録します。

番号 日付 品目 種別 単価(円) 数量 地域名 店舗名
1 2020/02/29 ラーメン 麺飯類 650 1 西東京 久我山店
1 2020/02/29 餃子 冷菜・点心 450 2 西東京 久我山店
1 2020/02/29 ビール ドリンク 630 3 西東京 久我山店
2 2020/02/29 ラーメン 麺飯類 650 1 西東京 久我山店

ここでまた、一部の属性を取り出して以下の述語を考えてみます。

(品目) は、 (種別) である。

これに登録済みのデータを適用し、命題にしてみます。

ラーメン は 麺飯類 である。
餃子 は 冷菜・点心 である。
ビール は ドリンク である。
ラーメン は 麺飯類 である。

同じことを2度言っていますね
前回、事実は一度言えば十分であると述べました。
そうでなければならない理由は、この三千世界に遍在する多重管理による問題と同じです。
例えば、以下のような間違った命題が混入するのを避けるためです。

ラーメン は ドリンク である。

「種別」属性と同じく品目に従属する、「単価」属性においてはもっとシビアな問題が発生するかもしれません。
そこで、設計を見直して以下のようにしました。

番号 日付 品目 数量 地域名 店舗名
1 2020/02/29 ラーメン 1 西東京 久我山店
1 2020/02/29 餃子 2 西東京 久我山店
1 2020/02/29 ビール 3 西東京 久我山店
2 2020/02/29 ラーメン 1 西東京 久我山店


品目 種別 価格(円)
ラーメン 麺飯類 650
餃子 冷菜・点心 450
ビール ドリンク 630

同じことを一度しか言わなくなりました
同時に閉世界仮説により、『ラーメンはドリンク説』も完全に否定されました。

また、よく見ると以下も潜んでいるのでそれも一度しか言わなくなるようにします。

この日のレシート番号 1 番は 久我山店 に発行しました。
この日のレシート番号 1 番は 久我山店 に発行しました。
この日のレシート番号 1 番は 久我山店 に発行しました。
番号 日付 品目 数量
1 2020/02/29 ラーメン 1
1 2020/02/29 餃子 2
1 2020/02/29 ビール 3
2 2020/02/29 ラーメン 1


品目 種別 価格(円)
ラーメン 麺飯類 650
餃子 冷菜・点心 450
ビール ドリンク 630


番号 日付 地域名 店舗名
1 2020/02/29 西東京 久我山店
2 2020/02/29 西東京 久我山店

ところで、ここで行った作業によって設計がリレーショナルモデルにとってよりふつうのカタチになったので、この作業も Normalization と呼べるでしょう。
先程の作業結果を前提にしており、段階的なカタチを区別するために
最初のものを 1st Normal Form 、今出来たものを 2nd Normal Form と呼び習わすことにします。

もう気になっていたこと

もう気づいていましたが、まだ2回同じことを言っているところがあります。

久我山店 は 西東京 エリアの店舗です!
久我山店 は 西東京 エリアの店舗です!

以下のとおり設計を見直します。

番号 日付 品目 数量
1 2020/02/29 ラーメン 1
1 2020/02/29 餃子 2
1 2020/02/29 ビール 3
2 2020/02/29 ラーメン 1


品目 種別 価格(円)
ラーメン 麺飯類 650
餃子 冷菜・点心 450
ビール ドリンク 630


番号 日付 店舗名
1 2020/02/29 久我山店
2 2020/02/29 久我山店


店舗名 地域名
久我山店 西東京

ところで、ここで行った作業も Normalization と呼べるでしょう。
先程の作業と同じような基準で、同じようなことをした気がしますが、
先程の作業で一意キーにばかり気を取られていた自分を戒めるためにも、
今出来たカタチをきちんと区別して 3rd Normal Form と呼び習わすことにします。

おわりに

リレーショナルモデルのあるべきカタチに導かれて、無事に第3正規形( 3rd Normal Form )までたどり着けました。
論理設計は、だいたいここまでやれば大丈夫です。
設計はもちろん、リレーションを操作する場合も考えれば、
「正規化ルール」というカードをあえて持たないことが、最高の手なのかもしれません。

*1:SELECT が UNION のように一意性を保つ結果を返してくれると随分良くなると思います。 今は、 SELECT DISTINCT と意識的に書かなくてはいけません。 SELECT ⇔ UNION ALL、 SELECT DISTINCT ⇔ UNION というように表記の対象性を損なっているし、一意性は常に保つべきなので、 SELECT ALL ⇔ UNION ALL(一意性を保たない)、 SELECT ⇔ UNION(一意性を保つ) というようにすべきかと思います。