MySQLアドベントカレンダー 6日目の記事になります。
私自身、アドベントカレンダーへの参加は初めてなため、若干緊張しております。
SQL文、書いてますか…？
Railsでも、Laravelも、ORM記述が増えてきている昨今……
皆様はSQL文、書いていますか？
割と今はSQL書かなくてもORMがいい感じにやってくれるようになってWebサービス作るときにもSQL文を書くことは少なくなってきたかなと思います。
また、Firebase等によるサーバーレス設計、NoSQLアーキテクチャにより、SQLはかなり遠い世界のものに。
今回は、そんな今の時代にデータベース設計を行う基礎について、自分なりに考えた記事になります。
データベースを選ぶ
まずは、リレーショナルデータベースを選びましょう。
なお、これらの３つには属性はありません。
とりあえず無料で使えるなら以下の３つ。
- PostgreSQL
- MySQL
- SQLite
これらはすべて、基本的なコマンドを備えて操作できます。
SQL文については割愛。今回のテーマは「ポケモンマスタ入門」なので。
ポケモンマスタとは
ポケモンマスタとは、その名の通りポケモンの情報が格納されたマスターテーブルなります。
拡張性も考えた設計を考えましょう。
ポケモン図鑑は単純なCSVで表せる
赤や緑の頃、ポケモン図鑑に記載されている情報は主に以下の通りでした。
- ポケモンNo
- 名前
- よみ
- 属性
- 身長
- 体重
- オーキドのコメント
- 分布（もしかしてカンマ区切り…？）
このように、一通りすべて埋めることができる簡単なテーブルの場合、我々は無理してSQLを使用する必要はありません。
単純な構造なので、すべてのポケモンの情報はCSVで書き表すことができます。
？？？「ポケモン図鑑の完成じゃ…」
以上、ポケモン図鑑の構造についての記事でｓ...
まだ終わりません！！！
リレーショナルデータベースとは
我々人類は、効率的なデータ管理を目的としてリレーショナルデータベースを開発し、それを使用してきました。
その歴史は深く、1970年、ポケモンが発売される25年もまえ。数学者であるE.F.Codd氏によってリレーショナルデータベースが提唱されました。
なぜE.F.Coddが、数学者がこれを定義したのか。リレーショナルデータベースのコアとなる理論は「集合論」によるものです。
その後、2度に渡り論文が提出され、初期のデータベースであるIBM System Rが開発されました。
開発が進んだものが今の時代のRDBMSなのです。
さて、これらのCSVをRDBMSに置き換えるとどうなるか…
以下のような図になります。
「これならCSVで書くのと何も変わらないじゃないか！！」と思ったポケモンマスターを目指す皆様、そのとおりです。
見ての通り、なにも変わりません。むしろ処理速度とメンテナンス性、あとは開発のしやすさを考えるとむしろCSVのほうがいいぐらいになります。
正規化
正規化とは、データを使いやすくすることを言います。主に正規化によって得られる効果はいくつかあります。
主に３つ、第一正規化、第二正規化、第三正規化とあり、それぞれの手順を踏むことでデータベースの保守性は高くなります。
しかしながら保守性が高くなる一方、リレーションに強く依存する形となり、結果的にはSQL文自体が複雑となります。つまり、ソフトウェアの変更の際のSQL文に対する管理コストが高くなり、保守コストも高くなってしまいます。（凄腕エンジニアがいれば別ですが。）
- データの冗長性を排除し、多重管理にならないようにする
- 不整合を排除する。
- 機能拡張や、属性の変更に強くなる
第一正規化（最低限、これだけでもやりましょう）
第一正規化は主に、繰り返しとなる、同じ項目を排除するために使われます。俗に言う「冗長性の排除」がこれに相当します。
ここで、先程の項目リストを、金銀時代のに変更してみます。
リストに、大きな変更があります。
キャラによっては属性が一つです。一方で、属性を2つ持つキャラも存在します。
この仕様変更を考えると、属性は今後も増える可能性が懸念されます。
また、属性には新たに「はがね」と「あく」が追加されました。
きっと、同様に属性は今後も増えることでしょう。
上記の内容をもとに、属性の項目はポケモン1体に対して「1対多」の関係が成り立ちます。
この関係のことを、リレーションと呼びます。
さて、関係性が影響することがわかったところで、これらの対応をしましょう。まずは属性を別のテーブルに書き出します。
このとき、プライマリキーとなる「絶対に重複しない共通の項目」を定義します。ポケモンには幸い、図鑑番号が振られています。「絶対に重複しない共通の項目」として使用できるため、これを紐付けていきます。
この図がER図と呼ばれ、DB設計の世界では重要なポジションになります。
今の図では、ポケモン1体に対して、複数の属性が定義できるようになりました。これが1対多の形です。これは、よく使う形にはなります。
正規化について
第二正規化、第三正規化は結構内容的にも難しい話になってくるので、ココでは省略したいと思います。
真面目に設計する
複数登録される項目の排除
先程のシートをよく見てください。まだ、別テーブルに外出しできそうな部分がありますよね。
さて、それらの部分を外に出していきます。
先程の表と比べて、属性が、さらに外に出ました。これを外に配置することで、様々なメリットを得られます。
たとえば属性の名称が再定義された場合、すべて入れ替えるのはめんどくさいので、このように定義するのみのテーブルも必要となります。
中間テーブルの存在
また、注目していただきたいのは、このpokemon_typeテーブルです。これはポケモンの種類と、それに属するものを直接紐付けます。
もともとポケモンの種別とタイプの種別は多対多の関係になりますが、SQL上で多対多の館系を作ってしまうとメモリを大幅に食うことや、SQLの難読化が進むことがデメリットとして考えられます。
その場合はお互いにプライマリキー同士の、存在する組み合わせのみ保存される、中間テーブルというものを使用します。
深くは触れませんので、興味がある方は検索してみてください。
さて、上記のこれは分布エリアにも言えることで、仮に生態系が変わり、分布エリアが増えたと仮定しましょう。その場合、分布する場所を個別に追加する必要があります。同時に、複数箇所に分布した場合、管理が悪化します。
マスターテーブルを活用する
これも同様に、別テーブルに書き出しましょう。
フル設計
ある程度きれいになってきました。これはあくまでポケモンの図鑑、そうデータベースの基本となるマスターに該当します。
捕まえたポケモンは、管理しなければなりません。
管理するためには・・・・ポケモンセンターのボックスが必要です。
捕まえたポケモンには以下の項目があります。
- Lv
- ニックネーム
- 捕まえたところ
- 身長
- 体重
- 覚えている技
- 各パラメータステータス
これらを紐付けて、何時でも参照可能なデータベースを構築します。持っているポケモン全ての個体情報を見れるって素晴らしいですね！
よく見ると、これらをベースにボックスを作るにあたって、捕まえたポケモンの個体に依存する項目で、一概に言えないような身長、体重といった情報は適切ではないので、マスターからは削除します。
さて、どうでしょうか。
比較的、管理しやすいDBになったと思います。
命名規則として、各テーブルのマスターとなるもの、そして1対多で繋がる場合のプライマリキーとなるものはINT型、名称はidとするのが一般的で、一方で関係キーなどはテーブル名＋idとするのが一般的ですね。
NotNull、INT側や文字列型などの指定はこの段階で細かく決めておくべきでしょう。
逆に、データの形が決まらないということは、つまり作りたい製品がなんなのか見えていないということです。
その場合は再度、仕様から見直して見る必要があるでしょう。
まとめ
一見単純そうに見えるデータでも、そのデータの利用先がポケモンセンターのボックスであったり、共通のマスターとして動作する部分は結構あるのですよね。
皆様の周りでも、データが多重管理になっているようなもの、結構見かけませんか・・・？
今一度、メンテナンスが行き届いてない、付け焼き刃のDBを見直してみてはいかがでしょうか。
さぁ、これで君も、ポケモンマスターだ！！
↑これが言いたかっただけです。