概要
テーブル設計のやり方について備忘録と頭の中の整理のために残しておきます。
やることはまず完成とは何がどういう状態なのかを定義すること、
テーブル設計ではテーブルが完成しているとはどういう状態かを定義します。
その後完成のために必要なタスクを抽出し行なっていきます。
DBはMySQLを想定しています。
テーブル設計の完成の定義
- 開発するソフトウェアに必要なデータがMECEで永続化できる構造を定義すること
必要なタスク
- UIを元に必要なデータを抽出する
- 抽出したデータを元にRDBMSの構造に当てはめる
具体的な流れ
タスクを具体的に実行していく方法を説明します。
1.必要なカラムを定義していく
例：Qiitaのようなサービスを作る場合
枠で囲った部分は記事の一覧がリストで表示されます。
記事の一覧は記事を複数集めたものなのでまずは一つの記事がどのような要素で構成されているのかを確認しましょう。
トップページの一覧に表示されている記事を構成するのは
- プロフィール画像
- ユーザー名
- 記事タイトル
- 投稿日
- タグ
- LGTM
- ストック
があるとわかります。
他にも必要なカラムを定義します。
記事の一覧を定義したので、次は記事の詳細ページを定義します。
記事の詳細で使われるデータは記事の一覧と共通する部分が多くあります。
記事の詳細にのみ必要なデータをピックアップします。
新たに必要とわかったのは
- 更新日
- 閲覧数
- 記事本文
です。
更新日と記事本文は記事にまつわるデータなのでPostsテーブルに定義し、閲覧数は分析などにも使われるためViewsテーブルに定義しましょう。
画面右サイドメニューの目次についてはテーブルにしなくても本文の見出し1をフロントで処理することで実現できるのでテーブルにしません。
また左サイドメニューのSNSボタンを使うことで自分たちのサービスのデータが変更されたり作成されたりすることはないのでテーブルは作成しません。
これらを大まかにテーブルに分けると下記のテーブルたちが少なくとも必要とわかる。
タグやストック、ユーザーとポストの紐付けには一般的に中間テーブルが必要なのでそこも追加する。
テーブル名キャメルケースで複数形、中間テーブルはアルファベット順でテーブル名同士を繋げる
カラム名は次の項で触れますがスネークケースでつけます
- Usersテーブル
- プロフィール画像
- ユーザー名
- Postsテーブル
- 記事タイトル
- 投稿日
- Tagsテーブル
- タグ名
- Stocksテーブル
- ストック名
- ストック追加日
- Lgtmsテーブル
- user_id
- post_id
- PostsUsersテーブル（中間テーブル）
- post_id
- user_id
- PostsTagsテーブル（中間テーブル）
- post_id
- tag_id
- PostsStocksテーブル（中間テーブル）
- stock_id
- post_id
他にもまだまだ必要なカラムは出てくると思いますが、UIから必要なカラムを定義する作業を紹介できたので次にデータ型を決めていきます。
カラムの詳細を決める
おおよそ必要なカラムがわかったのでテーブルの設計図を作ります。
まずは表に書き出すと整理できて良いと思います。
データ型の決め方は
ID関連 → integer
テキスト関連（タイトルやディスクリプションなど） → string
記事本文 → text
フラグ系 → integer（boolよりこちらの方が拡張性があるため）
作成日、更新日 → timestamp
で決めています。
qiitaのひと記事あたりの平均文字数は5700文字のようです。（最大で55万文字の記事もあるようです。）参考記事：結局Qiita記事ってどれぐらい書けばいいのさ
記事本文は3万文字あれば十分と思われるのでtext型にしています。
フラグ系があった際はboolよりintegerの方が良いです。
例えば有料プランがあるサービスを作る際に
ユーザーが有料プランか否かを判定して表示するデータを切り替える処理がある場合、条件分岐的にはついついboolで実装したくなります。
しかしユーザーのプランが2つ以上に増える可能性がある場合、boolで実装しているとデータ型を変えることになります。
そのためデータの種類が増える可能性がある際はintegerの方が無難です。
中間テーブルやLGTMテーブルには外部キーをつけています。
ユニーク制約をつけるカラムは以下になります。
- Stocks
- user_idとname
- PostsTags
- post_idとtag_id
- PostsUsers
- post_idとuser_id
- StocksPosts
- stock_idとpost_id
Stocksのuser_idとnameについては同じユーザーが同じ名前のストックを作成できないようにつけます。
他のテーブルは中間テーブルです。同じ組み合わせが登録されてないようにつけておきます。
一旦ここまでできたらチームのメンバーとレビューしてブラッシュアップしていくことでいいテーブルができると思います！
UIがあれば必要なデータがわかりやすいのでこのやり方だとカラムを過不足なく定義できるのでおすすめです。