WEB開発から見たDB設計(テーブル設計)

はじめに

今まで接してきた中で、仕事でテーブル設計をする人がDB設計のスペシャリストだったことはありませんでした。
大抵の人は、ネットでテーブル設計の方法を調べて、エンティティの抽出や正規化など初心者にはハードルの高いことを見て、適当にテーブルを作ってしまうそうです。(カラムが200個あったテーブルを見たときは卒倒しそうになりました・・・)

そこで、自分がWEBのサーバを開発したときのテーブル設計の手順とテーブル設計で気を付ける点を紹介します。
自分の独断と偏見に満ちた方法なので100%正解というわけではないので、一つの方法としてみてくださればと思います。

テーブル設計の思想

当然ですがDBを使うときは、必ず目的ありきでDBを使います。
今回は、WEB開発から見たテーブル設計なので、ユーザまたはWEBサーバに提供する機能のためにDBを使用します。
そのため、ここではユーザまたはWEBサーバに提供する機能からテーブル設計がスタートして、データの分類ごとにテーブルにまとめます。

テーブル設計の手順

1. ユーザまたはWEBサーバに提供する機能を決定する
2. 全てのリクエスト・レスポンスデータを書き出す
3. 共通するリクエスト・レスポンスデータを1つにする
4. リクエスト・レスポンスデータのグループにまとめる
5. 最終的に1つになるまでグループにまとめる
6. グループごとにテーブルを分ける
7. 内部で使用するデータを格納するテーブルを考える

テーブル設計の詳細

1.ユーザまたはWEBサーバに提供する機能を決定する

使用者に必要な機能・リクエスト・レスポンスを考えます。
厳密にはテーブル設計ではなく、アプリ設計の範疇なので詳細は割愛しますが、極力簡単な仕様を考えるほうが良いです。
具体的に使用者がアプリを使用しているところをイメージして、使用者にほぼ負担がなく、アプリの処理が簡単になる場合には仕様を変更するのも手です。

2.全てのリクエスト・レスポンスデータを書き出す

WEB画面であれ、REST APIであれ、複数の機能(URL)が必要になると思います。その複数の機能のリクエストデータとレスポンスデータを全て書き出します。この時、付箋でデータを書き込んでホワイトボードに張り付けていくと後々やりやすくなります。

3.共通するリクエスト・レスポンスデータを1つにする

ここが一番重要です。
2で書き出したリクエスト・レスポンスデータに全く同じデータが存在していると思います。その同じデータを1つにまとめます。
また、同じ意味なのに別の単語があれば、1に戻ってデータの表現統一をします。(例えば両方ユーザという意味なのにA画面ではuserとB画面ではmemberと2つの表記がある)
これにより、テーブル間でのデータ重複やデータの表記ゆれがなくなります。

4.リクエスト・レスポンスデータのグループにまとめる

ここが二番目に重要です。
3で精査したデータのグループにまとめます。3のデータをまとめてグループに名前を付けます。その際に、なるべく小さい単位でグループを考えてください。
グループ分けの際に複数グループに属するデータも存在すると思います。その時は、データを複製して両方のグループに属させるのではなく、識別番号等の明らかにデータやグループではない識別可能な番号だけを両方のデータに属するようにしてください。
この時のグループがテーブル、識別番号がリレーショナル(外部キーや関連付け)になっていきます。

5.最終的に1つになるまでグループにまとめる

4のグループをさらにグループにまとめてください。4の時と同様になるべく小さいグループにまとめてください。
最終的に1つになれば成功です。1つにならずに別れてしまったら、別れたデータは本当に必要なのかを考えてみてください。
データが分かれるということは使用者に提供する機能としてまとまりがない可能性がありますが本当に必要ならば別れたままでも問題ないです。
この時の最終的なグループがDBであったり、スキーマになります。

6.グループごとにテーブルを分ける

5のグループ・データをDBに紐づけていきます。
最上位のグループはDBまたはスキーマに対応します。使用できるDBのリソースでどちらかを選択してください。
次のグループからはデータの数と属するグループの数により、テーブルにするかどうかを決めていきます。
自身のグループの識別番号が、2～3つ以上のグループにある場合は、そのグループをテーブルにします。
自身のグループのデータが10～20以上の場合は、自身のグループに属しているもう一段下のグループをテーブルとします。
キーに関しては、グループ名に必須かつユニークなもの(グループを構成するのに必須のもの)を指定します。

7. 内部で使用するデータを格納するテーブルを考える

個人的には、内部で使用するデータを格納するテーブルはあまり好きではないのですが必要となる場合もあります。
その時は、対応するグループにデータを入れるまたはこのデータのみのテーブルを作成します。

テーブル設計の注意事項

この方法に限らずテーブル設計の時に以下を気を付けています。

• 文言の統一
  • 文言がテーブル間でまちまちだとテーブル間の紐づけが難しいです。○○番号を表現するときにNoやNAME、IDなどまちまちになりやすいです。
• テーブルの責務の平等
  • 一つのテーブルにカラムを詰め込みすぎないで、ある程度のカラム数があるなら別テーブルに分けるようにします。
• テーブル間のポリシーの統一
  • これが一番難しいですが、グループにまとめるときに納得できる理由でデータをまとめます。
• データの重複をしない
  • 同じ意味のデータを重複して持たないようにする。

テーブル設計の具体例

具体例として、組織の登録・表示、ユーザの登録・表示を考えてみます。

1.ユーザまたはWEBサーバに提供する機能を決定する

必要な機能

• 組織の登録

  • リクエスト
    • 組織名、郵便番号、住所、パスワード、メモ、メンバー
  • レスポンス
    • 組織ID

• 組織の表示

  • リクエスト
    • 組織ID
  • レスポンス
    • 組織名、郵便番号、住所、パスワード、メモ、メンバー

• ユーザの登録

  • リクエスト
    • 名前、誕生日、郵便番号、年齢、住所、好きなもの、メモ、パスワード
  • レスポンス
    • ユーザID

• ユーザの表示

  • リクエスト
    • ユーザID
  • レスポンス
    • 名前、誕生日、年齢、郵便番号、住所、好きなもの、メモ、パスワード

2.全てのリクエスト・レスポンスデータを書き出す

• データ
  • 組織データ
    • 組織ID、組織名、郵便番号、住所、パスワード、メモ、メンバー
  • ユーザデータ
    • ユーザID、名前、郵便番号、住所、パスワード、メモ、好きなもの

3.共通するリクエスト・レスポンスデータを1つにする

パスワードと郵便番号、住所が重複するため1つにまとめます。
また、メンバーとユーザIDは意味的に同じものなので1つにまとめます。

• データ
  • 組織ID、組織名、郵便番号、住所、パスワード、メモ、ユーザID、名前、メモ、好きなもの

4.リクエスト・レスポンスデータのグループ分けをする

• 組織データ
  • 組織ID、組織名
• ユーザデータ
  • ユーザID、名前、好きなもの、メモ
• 場所
  • 郵便番号、住所
• パスワード
  • パスワード

5.最終的に1つになるまでグループ分けをする

• 組織人員データ
  • 組織データ
    • 組織ID(1)、組織名、(2)、(3)
  • ユーザデータ(2)
    • ユーザID、名前、好きなもの、メモ、(3)
  • 場所データ(3)
    • 郵便番号、住所
  • ログインデータ
    • (1)、(2)、パスワード

6.グループごとにテーブルを分ける

• 組織人員DB
  • 組織テーブル
    • 組織IDカラム、組織名カラム、ユーザテーブルへのリレーション用カラム(ユーザID)、場所テーブルへリレーション用カラム(郵便番号)
  • ユーザテーブル
    • ユーザIDカラム、名前カラム、好きなものカラム、メモカラム、場所テーブルへリレーション用カラム(郵便番号)
  • 場所テーブル
    • 郵便番号テーブル、住所テーブル
  • ログインテーブル
    • 組織テーブルへリレーション用カラム(組織ID)、ユーザテーブルへのリレーション用カラム(ユーザID)、パスワードカラム

おわりに

テーブル設計の方法について書きました。このように書いてみると今まで感覚的には理解できていたものを表現するのがすごい難しかったです。
テーブル設計に失敗すると設計者がいなくなった瞬間にテーブルの扱いがめちゃくちゃになりバグやデータのライフサイクルが壊れる可能性があります。たとえテーブルの設計者がいたとしても、複数人でプログラムを作る場合などに文言の統一やグループ分けができていなければ、他の人がDBの情報を操作するときに誤った情報を取ってきたり、誤った情報を登録するなどを防ぐためにテーブル設計者に確認を取る必要があるため、無駄な労力を払うことになります。
誰がどう見てもわかりやすいDBは難しいですが、極力客観的に見返して知識0の状態でもわかりやすいDBを心掛けるようにしています。