---
name: database-schema-design
description: Design database schema for the project
---
# データベーステーブルデザイン指針
これは、RDBのデータスキーマを設計する際に、守るべきルールを記したものである。
このようなルールを設けた理由は、ルールの統一によってメンテナンスしやすくできるから、というだけでなく、DB Schemaデザインの際に、迷ったり、哲学の違いから揉めたりすることをなくするためである。
したがって、ここに書かれているルールが、自分の哲学と違っている、と感じる開発者もいるはずである。しかし、このプロジェクトとして設計、開発するものに関しては、ここで示されたルールに則って設計を行うように。
## システム
### RDBMSシステム選定
RDB(Relational Database)として、PostgreSQLを利用することを前提とする。
## テーブル設計
### テーブル名
- リソースを表す名詞の複数形を利用する
- `data` は複数形であるが、わかりづらいため利用しない
- 単複同型の単語は利用しない
### Primary Key
プライマリーキーのカラム名は `id` とする。`user_id` のようにテーブル名の単数形をつける流儀もあるが、ここではすべて `id` で統一する。
プライマリーキーの形式であるが、UUID v4 あるいは BigIntの値とする。プライマリーキーの形式は、プロジェクト全体で統一する。UUID と連続する数値のどちらを使うべきかは、プロジェクトの性格によって異なるが、IDをつかって生成順にソートする必要があるテーブルが有る場合には BigIntを、そうでなければ UUIDを利用するとよい。
なお、数値を使う場合、`Int` (32bit) ではなく、`BigInt` (64bit) を利用する。これは、将来桁あふれが発生するリスクを少しでも抑えるためである。
### カラム名の命名規則
カラム名はSnake Caseにする。`user_id` や `access_token` といった形式になる。
カラム名は、それを見ただけで何が格納されているのかがおおよそ想像できるものが望ましい。したがって、なるべく一般的な名称を用いる、プロジェクト内で同じ意味のカラムは必ず同じ名前にする。わかりやすさを最大限重要視する。
### 日時を表すカラム
日時を表すカラムは、`created_at` や `registered_at` のように、過去形の後に `_at` のPrefixをつける。
データ型は `BigInt` を利用し、Unix Timestampで値を扱う。その理由は、parseが簡単で、タイムゾーンによる影響を受けず、ほとんどの言語の時間を扱うライブラリで標準でサポートされているからである。RDBMSはDateTimeを扱う専用の型を用意しているが、タイムゾーンの取り扱いに差異があり、またタイムゾーンの値が、そのDBの設定（やOSの設定）に依存するため、ポータビリティが下がる恐れがあるためである。
ただし、タイムゾーンの関係のない、「日付」のデータに関しては、`Date`型を利用する。しかしその情報が、本当に日付だけで将来に渡って事足りているのかは、よく吟味する必要がある。たとえば「登録日」を保存しておきたかった場合でも、将来時間も必要になるかもしれないので、UnixTimestamp にしておいたほうが良いかもしれない。
### テキストカラム
テキストカラムは VARCHAR ではなく、TEXTを利用する。これは、PostgreSQLにおいては、2つのタイプの違いは、VARCHAR が文字列の長さをチェックすることだけであり、それ以外は同じであるためである。
#### created_atとupdated_at
すべてのテーブルには、レコードの作成日時を表す `created_at` と、最終更新日時を表す `updated_at` を用意し、作成時、更新時に自動でその時間がセットされるようにする。
この2つのカラムに関しては、日時を表しているが UnixTimestampではなく、`TIMESTAMP`型を利用する。
その理由は、これらのデータがフレームワークによっては自動的に扱われるため、その仕様に合わせるためである。
なお、この2つの情報は、純粋にロギングとトラブル時の問題切り分けに利用するにとどめ、サービス、システムのビジネスロジック上において、この値を使って何かを判断してはならない。
たとえば、`users` の `created_at`を利用して、ユーザーの登録日時を判断してはならない。もしユーザーの登録日時を利用する場合は、その代わりに、`registered_at`という別のカラム（Int型）を用意して利用する。
それは、`created_at`と`updated_at`のカラムが、データの移行など意図しないタイミングで変更される可能性があるからである。
### フラグを表すカラム
フラグを表すカラムは、`is_` や`has_` などのPrefixをつけることで、それがはっきりとフラグであり、boolean であることがわかるようにする。Prefixが`is_` なのか`has_`なのか、それ以外なのかは、フラグの意味によって適切なものを選択する。
### マジックナンバー
カラムに格納するデータとして、マジックナンバーの利用は絶対に避ける。
たとえば `orders` というテーブルに、注文の状況を表す `status` というカラムがあったとする。その場合、このカラムのデータ型をIntにして、1だったら注文完了、2だったら配達処理完了、のような設計をすることは絶対にしてはならない。
なぜなら、その場合、それぞれの数値が何を表しているかを知らなければ、内容を理解することができないからであり、「テーブルだけを見ても意味がおおよそ理解できる」という原則に反することになる。
したがって、`status`のカラムは文字列型として、`ordered` や `delivered` といった文字列を格納するようにする。
### NULL制約
原則としてNULLは許容しない設計とする。NULLを許容する場合は、NULLに意味がある場合のみとする。NULLに意味がある場合とは、NULLであることで明示的に、そのカラムのデータが存在しないことを表す場合である。
- relationを表すカラムで、そのrelationが存在しない場合にNULLを許容する
- 次回更新日付を表すカラムで、次回更新日付が決まっていない場合はNULLを許容する
などである。NULLを許容する場合は、コメントでその理由を記述する。
### デフォルト値
ビジネス要件的に明らかな初期値として設定可能な情報が存在する場合は、その値を設定する。
ただし、初期値値が不明で、明示的にレコード作成時に値を設定することがビジネス要件上必須な場合は、デフォルト値を設定しない。たとえば、医療系のシステムで、患者の体温を記録するテーブルがある場合、そのテーブルにはデフォルト値を設定すると、間違った情報が記録される恐れがあるので、記録しない。
### 論理削除と物理削除
原則として、ビジネス上の追跡可能性（トレーサビリティ）が必要なデータには論理削除を採用するが、最低限にとどめる。
論理削除を行う場合は `deleted_at` というカラムを用意し、そのカラムに削除が行われた日時を格納し、がNULLであれば削除されていない、とする。ただし、フレームワークによって異なる方法が取られていた場合は、それに準じた構造とする。
## Relation
テーブル同士のRelationを表現する場合は、外部キーを設定するが、その名前は、例えば `users` テーブルの `id` カラムを別のテーブルから指定する場合には `user_id` という感じで、テーブル名の単数形にカラム名を連結して表す。
Many to many のRelationを表す場合は、Relation Tableを定義するが、その場合は、例えば `users` と `roles` の場合であれば、`user_roles` のように、片方の単数形に他方の複数形を連結した形として、内部には `user_id` と `role_id` が含まれるようにする。どちらを単数形にするかは、テーブル同士の関係性を考慮し、より重要と思われるデータを選択する。
## インデックス
- 頻繁に検索条件として使用されるカラムにはインデックスを作成する。
- 外部キーには必ずインデックスを作成する
### 複合インデックス
- カーディナリティ（値の種類の多さ）が高いカラム、あるいは最も検索の絞り込みに効果的なカラムを先頭にする
- よく使用される検索条件の組み合わせに合わせて設計する
- WHERE句とORDER BY句の両方に最適化できるよう考慮する
## セキュリティ
- パスワードは必ずハッシュ化して保存（平文は絶対不可）する
- クレジットカード情報に関しては、ペイメントゲートウエイを利用し、カード情報は自社で保持しない
