database-schema-design

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句の両方に最適化できるよう考慮する

セキュリティ

• パスワードは必ずハッシュ化して保存（平文は絶対不可）する
• クレジットカード情報に関しては、ペイメントゲートウエイを利用し、カード情報は自社で保持しない