DB 設計は、思いついたテーブルを順に作っていくと、あとから「この列はどこに置くべきだったか」で手戻りが起きやすい作業です。この記事では、小さな受注管理システムを例に、要件の整理から DDL とドキュメントの作成までを 7 つのステップに分けて説明します。
1. 業務の言葉と要件を集める
最初にやるのは、テーブルを考えることではなく、業務で使われている言葉を集めることです。「顧客」「取引先」「得意先」が同じものか違うものかは、話を聞かないとわかりません。あわせて、次のような要件を書き出します。
- 何を記録するか（受注、出荷、請求、入金）
- 何を検索・集計したいか（取引先別の売上、未出荷の受注）
- どこまで履歴を残すか（価格の変更履歴は必要か）
- データ量の見込み（1 日の受注件数、保持期間）
2. エンティティを洗い出す
集めた言葉から、独立して管理する「もの」や「出来事」をエンティティとして取り出します。名詞がそのまま候補になりますが、属性にすぎないもの（「都道府県」など）とは区別します。
受注管理なら、顧客・顧客担当者・受注・受注明細・出荷・商品・カテゴリ・在庫・請求・入金が候補になります。
3. エンティティ同士の関係を決める
各エンティティの間に、1 対 1・1 対多・多対多のどれが成り立つかを決めます。「1 つの受注に明細は何件あるか」「1 つの商品は複数の受注に出てくるか」のように、両方向から確かめるのがコツです。
- 顧客 1 : 受注 多
- 受注 1 : 受注明細 多
- 受注 多 : 商品 多 → 受注明細を中間テーブルにして、受注 1 : 明細 多 : 1 商品 に分解
この段階で ER 図に描いておくと、関係の抜けや向きの間違いに気づきやすくなります。記号の読み方は ER 図の書き方と crow's foot 記法 を参照してください。
4. 属性と型を決める
エンティティごとに属性（列）を並べ、型を決めます。迷いやすいのは次の点です。
| 対象 | おすすめ | 理由 |
|---|---|---|
| 金額 | numeric(12,2) など固定小数点 | 浮動小数点は丸め誤差が出る |
| 日時 | タイムゾーン付き（PostgreSQL なら timestamptz） | サーバーや利用者の地域が変わっても解釈がぶれない |
| 状態 | Enum かマスタテーブル | 自由入力の文字列は表記ゆれが起きる |
| 文字列の長さ | 実際の最大長に余裕を持たせた程度 | 極端に大きい上限は入力チェックの役に立たない |
5. 正規化して重複をなくす
同じ情報が複数の場所に入っていると、更新のたびに食い違いが起きます。第 3 正規形までを目安に整理します。
- 第 1 正規形：1 つの列に複数の値を入れない（「商品1, 商品2」を 1 列に入れず、明細テーブルに分ける）
- 第 2 正規形：複合主キーの一部だけで決まる列を別テーブルに出す（明細に商品名を持たず、商品テーブルを参照する）
- 第 3 正規形：主キー以外の列で決まる列を別テーブルに出す（受注に顧客の住所を持たず、顧客テーブルを参照する）
ただし、受注時点の単価のように「その時点の値」を残したいものは、あえて明細側に持たせます。これは重複ではなく、別の意味を持つデータです。
6. キー・制約・インデックスを決める
- 主キー：すべてのテーブルに付けます。業務上の番号（受注番号など）は、変わる可能性があるなら主キーにせず UNIQUE にします。
- 外部キー：関係ごとに付け、親を消したときの動き（
ON DELETE）を決めます。 - NOT NULL・UNIQUE・CHECK：アプリ側のチェックだけに頼らず、DB でも守ります。
- インデックス：検索条件・並び順・外部キーの列に付けます。外部キーの列は DB によって自動で付かないので注意が必要です（外部キーにインデックスは必要？）。
7. DDL とドキュメントにして、レビューする
最後に、決めた内容を DDL とテーブル定義書にします。ここで設計とドキュメントを別々に書くと、すぐに食い違い始めます。1 つのテキストを正にして、DDL・ER 図・定義書を生成する形にしておくと、レビューも更新も楽になります。
Table order_items {
order_id bigint [not null, ref: > orders.id]
line_no int [not null]
product_id bigint [not null, ref: > products.id]
quantity int [not null, check: `quantity > 0`]
unit_price numeric(10,2) [not null, note: '受注時点の単価']
indexes {
(order_id, line_no) [pk]
product_id
}
}レビューでは、次の点を確認します。
- 主キーのないテーブルはないか
- 外部キーと参照先の型は一致しているか
- 外部キーの列にインデックスはあるか
- NULL を許す列は、本当に値がない場合があるか
設計とドキュメントを、1 つのファイルで
Joinery は DBML から ER 図・DDL・テーブル定義書を生成し、Lint で設計ミスを指摘します。