はじめに
それではポケモン図鑑ののテーブルもだんだん出来てきましたので、、
そろそろポケモン図鑑のDB設計をやっていきます。
普通の開発では多分真っ先にやることですので、良い子は真似しないようにしましょう。
実質パート６に当たるこの記事ですが、そういう意味でパート0です。
テーブル設計の手順
DB設計は以下の４つのステップで行われます。
- エンティティの抽出（必要なデータの洗い出し）
- エンティティの定義（テーブルやカラムを決める）
- 正規化の実行（データの冗長性をなくす）
- ER図作成
こちらの流れに沿って、DB設計のやり方をたどっていきます。
#1. エンティティの抽出
このフェーズでは「これから作るものはどんなもので、どんなページや機能があるか」思いつく限り洗い出します。
例えばこれから以下のような機能をもったポケモン図鑑をつくりたいとします。
（注意）以下の要件はフィクションであり、ネット上にはすでにこのようなポケモンの超優良なサービスが数多く存在しています。
- 登録されたデータを見るのは誰でも可能とする。
- 内容を投稿、編集するためにはユーザー（トレーナー）登録する必要がある。
- ユーザーはパスワードとIDなどでログインする必要がある。
- 登録されたポケモンのデータを見ることができる
- どのような技が覚えることができるか確認できる。
- オリジナルのポケモン図鑑に登録されたステータスをもとにソートしたり絞り込み検索ができる
- 生息分布がどこか見ることができる
- ユーザーがオリジナルのパーティーを組んで投稿ができる。
- それを他のユーザーが閲覧し、いいねをつけれる。
- いいねのついた投稿がランキングで表示される。
- etc...
自分が作りたいものorクライアントが求めているものを明確化し、必要になる機能をひたすら洗い出します。
自分の考えを吐き出したりクライアントから要望を引き出し、整理してみます。
紙に書き出すのも良さそうですし、僕はよく**マインドマップ（X-MindやMindNodeなど）**をつかってます。
上記のサービスを作るとしたらどのようなテーブルが必要で、どのようなカラムが必要なのか...って言うことを決めていきます。
そのときに大事なのはユーザーがどのようなシナリオでそのサービスを利用するかと言うのを考えます。
例えば今回の要件の場合、
図鑑に登録されたピカチュウの情報を見るためには、ユーザー登録した人や管理者がポケモンの登録（編集）をしていなといけません。そしてポケモンを登録するには以下のようなシナリオが考えられます。
- ユーザー登録画面でパスワードとメールアドレスで新規登録。
- ログイン画面で登録した情報をもとにログインする。
- トップページからポケモン図鑑画面にいき、ピカチュウを検索しピカチュウのページへ。
- ピカチュウのページで編集を行う。
自分が考えたポケモンの編成や習得させた技を公開するには、登録されたユーザーが投稿画面で投稿しなくてはいけません。ということは..
- ログインしたユーザーがトップページ→おすすめパーティ→投稿画面へ。
- 使用するポケモンを決める
- 習得した技を選択し、投稿画面で情報を登録する。
覚えさせた技を技を選べるようにしなくてはいけないですから、技のテーブルが必要ですね。（最初は技名だけでいいかもしれませんが）
それら洗い出したデータをもとに、
- 「開発するシステムの要件を満たせるか？」
- 「本当にそれは必要なのか？」
をいうのもいったんここで考えてみます。
エンティティの定義
まず、エンティティという言葉は日本語で「実態」。
IT的な意味合いでは「一つの物事を表すひとまとまりのデータの集合」。
DBでいうとカラムやテーブルのことです。つまり...
エンティティの定義 = カラムやテーブルを決める
ということになり、**「各テーブルがどのようなカラムを持つか？」**というものを定義していくフェーズになります。
カラムやテーブルの命名、各テーブルのキーの定義などもここで行っていきます。
##テーブルの詳細設計
###命名規則
洗い出した各エンティティ名を英語に変換し命名するのですが、いくつか約束事があります。
- 半角アルファベット、半角数字、アンダーバーしかつかわないこと。
- 基本的には日本語を英語に変換し命名すること。
- テーブル名は複数形（items, usersなど。）、カラム名は単数形(item user)とすること。
- テーブル名が英語であれば複数形で書くことができること。出来ない場合は
- テーブル1, テーブル2や、カラム１,カラム２のような内容が想像しにくい名前をつけないこと。
- 構文エラーが起きるため。IF, US , LIMIT , DATEなど予約後を使用して命名しないこと。
- アルファベットと数字の組み合わせの場合、アルファベットを先に書くこと。
- テーブル名が同じドメインの中で重複していないこと。
などですね。
自分のように英語力の乏しい者がテーブル名やカラム名を日本語→英語に変換して決める時は、Google先生や DeepLで翻訳するか、「 codic 」というサービスがよさげです。
特にcodicは通常の翻訳よりもマイルドな表現をしてくれたり、パスカルケース、キャメルケース、ハイフンつなぎかアンダーバーつなぎかも選択できるので、変数名を考えるときによく重宝して使っています。
###カラムに型をつける。
カラムの型については、以前書いた記事にチートシートを書いておいたのでそちらを参照。
- idには
INT型 - nameには
varchar型
といったように適切な型を割り振っていきます。
###制約条件やインデクッスをつける
###制約
前提として、制約は可能な限りつけるようにしましょう。
こちらも以前書いた記事に制約の種類などをのせております。
- NOT NULL
- 基本的に全部つけていい
- つけない例は、備考欄や任意で投稿するコメントなど。
-
UNIQUE
- 重複してはまずいものにつける
- 氏名など同姓同名がありえる場合はつけない。
- 外部キー制約
- リレーション先（関係のあるテーブル）先にレコードがあることを保証する制約。
###インデックス
インデックスとは本で例えるとほんの背中についてる見出しのようなもので、データベースの検索を高速化するものです。
ポケモン図鑑の場合はポケモンの名前( name )から検索をかけることが多いと考えられるため、pokemonsテーブルのnameカラムにインデックスを付けるとよいかとおもいます。
- 検索のキーになるカラムにつける。
- primary keyや外部キーにはつけなくていい。（自動でつくから）
####インデックスのデメリット
結論から言うと更新処理が低速になります。
インデックスの更新作業が発生するため。アップデート文などを実行するとINDEX一覧の更新処理というものが裏で発生するため、更新処理が遅くなります。
そのため、インデックスを貼るのは必要最小限にしましょう。
###外部キー制約
外部キー制約( FOREIGN KEY制約 )とは..
親テーブルと子テーブルの2つのテーブル間でデータの整合性を保つために設定される制約です。
####外部キー制約の貼り方の注意点
例えば、userが退会した場合に、ユーザーの情報を物理削除（レコードを本当に削除）するのか、論理削除（ユーザーテーブルに削除フラグを設けて論理的に削除）するのかによって外部キー制約の貼り方が変わってくる個人情報の保護方針など設計以前にシステム要件がからんでくるため、クライアントに確認をとって適切なほうをつかいましょう。
##3点セット
id, created_at, update_atの各テーブルにつける3つのカラムです。
- 【主キー】id
- INT型のAUTO_INCREMENTにすること。
- 「〇〇_id」は外部キーに命名するため、「id」と命名する。
- 【作成日・更新日】 created_at / update_at
- **
DATETIME型**にすること。 - 日付だけでなく、時間なども含めることができるため、「_date」 ではなく 「_at」 と命名すること。
##SQLアンチパターン
いい加減なテーブルやカラムをつくったりすることで、拡張性が悪い、内用がか分かりづらい、更新や変更によってアプリケーションが壊れる危険を孕む良くないDB設計は避けなくてはいけません。
このような設計パターンを「SQLアンチパターン」というらしく、解決策もあります。
「SQLアンチパターン」という本がオススメらしいので、いずれ読んでしっかり勉強したいところです。
アンチパターンはなかなか数が多くてじっくり勉強していく必要がありますが、ひとまず今手元にある教材やググって調べた内容で、ポケモン図鑑を作成するに当たり陥りそうなアンチパターンや、リファクタリング（外部からわからないようにソースコードの内部構造を変えること）が必要なケースを考えます。
###列持ち（重複データのカラム待ち）
ポケモンは、ヒトカゲのように'ほのおタイプ'しか持たないものもいれば、フシギダネのように'くさタイプ'と'どくタイプ'の複数を持つものがいます。こちらのようなテーブを例にします。
長年ポケモンは最大で２つのタイプしか持てない仕様になっていますが、仮に使用が変更されてポケモンが全部で３つ、４つとタイプを持てるようになったときに問題が起きてきます。
- タイプを3つ持つことができるようになった場合、カラムを追加する工程が生まれシステムの拡張性が悪い。
- タイプを一つしか保持していない場合、それ以降のテーブルにはnullや空の文字列が入るため、結果としてシステム側で取得データごとにnullのチェックなどが入る。
等が挙げられ厄介なのですが、カラムではなく行でデータを保持することで改善が可能です。
####【改善策】行でデータを保持する
重複したデータの数が増えた場合でも行でデータを保持していれば、IDを追加し、新規で行を登録するだけで対応が可能なため、場合によっては、システムの改修が不要となる場合もあります。
また、通常のシステム改修でも入力チェックを変更するだけで済む場合が多いです。
また、行でデータを保持することで、必要な数だけ行を登録することが可能となり、nullによるデータの登録が不要となります。
ポケモンのタイプを登録するテーブルの場合、タイプが1つだけであれば1行登録、タイプが2人であれば３行登録..といった具合に、所持できるタイプが増えた場合が増えた場合での改修もしやすく、nullが発生しません。
なお、行でデータを定義した場合は、以下のようなテーブル構成になります。
ポケモンを例に例えましたが、他にも複数のサークルに所属できる「生徒」と、複数の生徒が所属できる「サークル」などの結びつけなど、n:nの関係を結びつける際にも便利です。
###リファクタリングが必要な項目・データベースの不吉な臭い
データベースリファクタリングという本が非常に名著らしく、**リファクタリングが必要になる項目」**について扱っているらしいので、さっそく欲しい物リストに入れました。
主にこういった「不吉な匂い」を感じたらたら
- どのくらいの効果があるのか？
- どれくらいの工数がかかるのか？
- 今すぐやるべきなのか？
- 他に任せることはできないのか？
- 時間をかけてでもやるべきなのか？
- 捨てる決断が必要か？
という点を検討して優先順位付けを行う必要があります。
###複数の目的に使われるカラム
レコードの属性に合わせて値の意味が変わるカラム
- 「joined_at」の場合会員だと入会日、スタッフだと入社日
- 「登録日」だとサービスに登録した日、図鑑に登録した日
###複数の目的に使われるテーブル
「Usersテーブルに会員、管理者、事業者などが混在している」といったような、ひとつのフォーマットに複数のデータが入っているテーブル。
特徴は、データの種類ごとにNULLになるカラムが決まっている。
###冗長なデータ
冗長性とはIT的な意味合いでは、データベースやデータストレージのテクノロジーに同じデータが保持されている状態。
データベースリファクタリングでは、他のシステムとのデータ重複を挙げています。
生年月日と年齢カラムがあった場合、1年経ったときに年齢カラムが事実からずれてデータの整合性が取れなくなる。
###カラムの多すぎるテーブル
memo1、memo2、memo3…memo99 など複数のエンティティの責務を束ねている可能性があるテーブル。
上記の「複数の目的に使われるテーブル」もカラムが多い事が多い。
###行の多すぎるテーブル
中にどのくらいのデータが入るのか全く意識しないために想定外のデータ量が入ってきてしまうというパターン。
設計時に、これから入れるデータの量が数十件単位なのか、万単位になるのか想定しておき、本当にデータの行数が大きくなるテーブルならパーティションなどを検討する
###「スマート」カラム
スマートカラムとは, データ内の位置によって表す概念が異なるカラムのこと。（9から始まるidは管理者、1から始まるidはユーザーなど）。
どこかの時点でそれを構成するデータフィールドに整理しなおし, データベースがそれぞれを独立した要素として簡単に扱えるようにする必要があります。こういうものはちゃんとカラムに分けて、個別に扱えるようにしてあげましょう。
###変更の恐怖
データベースの変更や更新によって、「アプリケーションが壊れる」 、という恐怖があって手を付けれない状態。
ＤＢのスキーマを変更する必要がある案件＝工数がかかる案件といわれていたようです。
##定義してみる
それではポケモン図鑑のエンティティを定義してみます。
- 【テーブル】pokemons
- 【カラム】id INT AUTO_INCREMENT
- 【カラム】name VARCHAR(6)
- 【カラム】category VARCHAR(10)
- 【カラム】type VARCHAR(10)
- 【カラム】height FOAT NOT NULL
- 【カラム】weight FOAT NOT NULL
- 【カラム】description TEXT CHECK(length(description) > 5)
- 【カラム】weight FOAT NOT NULL
- 【カラム】create_at DATETIME DEFAULT 'now'
- 【カラム】update_at DATETIME
こんな感じでしょうか？
長くなってしまったので、続きは次回にしていきます。
##過去の記事
現在までの操作を記事化してたら謎のシリーズ化し始めてます。