なんとな~くしあわせ?の日記

「そしてそれゆえ、知識そのものが力である」 (Nam et ipsa scientia potestas est.) 〜 フランシス・ベーコン

SQLもプログラミング言語…っぽい4

このシリーズも4回目になる。お題は以下の通り

テーブルの正規化

正規化とはなんぞや?

ここが分かりやすかった → データベースの正規化(正規形)とはなんぞや

以下、上のリンクに従って概要をまとめてみる

非正規形

わかりやすく言うとExcelで左のセルを縦に結合したとき、右に複数行のデータがあればそれは非正規形であると言える。データベースに配列型をぶっこもうとしている状態、アカンのである。

食品名 栄養成分 単位
精白米
炭水化物 168 kcal
エネルギー 37.1 g
ビタミン C 1 μg
D 1 μg
E 1 μg

上の例だと、「精白米」という1レコードに対して「炭水化物、エネルギー、ビタミン」という3レコードがくっついている。

この考えをそのままデータベースに適用しようとすると、配列型が用意されてないDBだとできない*1。それで、VARCHAR型に無理やりカンマ区切りでデータを入れたりするやらかし先生がいるかもしれない。「炭水化物,エネルギー,ビタミン」みたいな感じで…

実は、これはSQLの有名なアンチパターンで、「Jaywalking」でググるといろいろ出てくる。

www.slideshare.net

上のスライドには種本がある、自分はまだ読めていない

SQLアンチパターン

SQLアンチパターン


第1正規形

先ほどのExcelの結合部分を右の行数に合わせて分割する。繰り返し項目が増えるが、これでレコードがそれぞれ1行で表せる。

食品名 栄養成分 単位
精白米 炭水化物 168 kcal
精白米 エネルギー 37.1 g
精白米 ビタミンC 1 μg
精白米 ビタミンD 1 μg
精白米 ビタミンE 1 μg

第2正規形・第3正規形

この2つはそんなに変わらない気がする。表を独立する部分で切り出して行けば自ずとこれになる*2。自分が作った例では表が簡単すぎて分割できない…他のサイトを見てみてください。

外部結合の設定

SQLは上の要領で分割して粗結合にしたテーブルを外部結合して見たい情報を揃えるのが一般的な使い方だ。

以前取り上げた例をもとにやってみよう
SQLもプログラミング言語…っぽい - なんとな~くしあわせ?の日記

LEFT JOIN


Parentテーブル

主キー 整理番号
1 12345 山田 太郎
2 12346 山田 花子
3 12346 山田 花子
4 12347 山田 嘘子
5 12348 山田 良子


Childテーブル

整理番号 住所 郵便番号
12345 どこかの台 1丁目 131-0202
12346 すすきが原 3丁目 251-0003
12346 ニセコ 201-0003

Parentテーブルの整理番号とChildテーブルの整理番号が共通してるので、Parentテーブルを基本として左外部結合してみる

CREATE TABLE parent(`主キー` text, `整理番号` int, `姓` text, `名` text);

INSERT INTO parent(`主キー`, `整理番号`, `姓`, `名`)
VALUES (00001, 12345, "山田", "太郎")
, (00002, 12346, "山田", "花子") 
, (00003, 12346, "山田", "花子") 
, (00004, 12347, "山田", "嘘子") 
, (00005, 12348, "山田", "良子");

CREATE TABLE child(`整理番号` int, `住所` text, `郵便番号` text);

INSERT INTO child(`整理番号`, `住所`, `郵便番号`)
VALUES (12345, "どこかの台 1丁目", "131-0202")
, (12346, "すすきが原 3丁目", "251-0003") 
, (12346, "ニセコ", "201-0003");

このように、左に指定したテーブルが中心となる。そのため右に結合したテーブルにNULLが入ることもある。

主キー 整理番号 整理番号 住所 郵便番号
1 12345 山田 太郎 12345 どこかの台 1丁目 131-0202
2 12346 山田 花子 12346 すすきが原 3丁目 251-0003
3 12346 山田 花子 12346 すすきが原 3丁目 251-0003
2 12346 山田 花子 12346 ニセコ 201-0003
3 12346 山田 花子 12346 ニセコ 201-0003
4 12347 山田 嘘子 (null) (null) (null)
5 12348 山田 良子 (null) (null) (null)

右に配置されるレコードが複数行ありえる場合、レコードが増えてしまう。それを防ぐためには、以下のようにレコードを一意に絞りこめるようにする。

LEFT JOIN child
ON parent.`整理番号` = child.`整理番号`
AND child.`郵便番号` = '251-0003'

書籍の紹介

とまあここまでは実は応用情報技術者試験の範囲のようだ。

以下の本のSQLのところを見るともっと網羅的な解説が載っている。

ニュースペックテキスト 応用情報技術者 平成29・30年 (情報処理技術者試験)

ニュースペックテキスト 応用情報技術者 平成29・30年 (情報処理技術者試験)

履歴管理

みんなやってることなので、Qiitaに記事がある

実際的な設計

これ読んどけば十分な気がする

たぶん大体のRDBの人々は1番目のテーブルにバージョン番号を持たせる方法を使っているのではないだろうか。2番目の履歴テーブルをもう一つ作る方法は結構めんどくさそう。

しかし、Stackoverflowの同様のスレッドを見てみると2番目を薦める人が多かった(そしてベストアンサーはRDBをそういうことに使うな!という解答。まあ履歴番号だけ持たせて、実装はGitに任せるとかのほうが正確なシステムにはよいのかもしれない。)

stackoverflow.com


*1:Postgresql先生にはあるようだ ぱせらんメモ - PostgreSQLで配列型のカラムを使ってみる

*2:コッド博士はこれをちゃんと定義して説明したから偉い、想像するだけなら凡人でもできる