テーブル設計の考え方とやり方 [入門編]

「基本から学ぶテーブル設計　超入門！」
https://modeling-how-to-learn.connpass.com/event/242944/
の発表資料。

- ２つの設計スタイルの違いを理解する
- 何を記録するか（資源・活動・当事者・規程）
- どう記録するか（テーブルの役割を単純に保つ）
- 基本ツール：CREATE TABLE文
- データ型と制約

1. テーブル設計の考え方とやり方 【入門編】 有限会社システム設計 増田 亨 現場から学ぶモデル駆動設計 第１４回 基本から学ぶテーブル設計 超入門！
2. お話しする内容 ① データベースを利用する目的 事実の記録・事実の利用 ② ２つの設計アプローチ 追記型と更新型 ③ 何を記録するのか 活動・資源・規程・当事者 ④ テーブル設計のやり方/学び方 CREATE TABLE 文、名前・データ型・制約 ⑤ テーブル設計の基本パターン 2
3. データベースを利用する目的 ① 発生した事実を記録する ② 記録した事実を利用する 3
4. ２つの設計アプローチ 追記型（イミュータブル） 発生した事実を追記していく 発生した事実の記録からテーブル設計を考える 更新型（ミュータブル） 発生した事実を使って最新の状態に上書きする 発生した事実の利用からテーブル設計を考える これから説明するのは追記型の考え方とやり方です 更新型とは考え方やり方が異なります 注意 4 （多くの書籍、ネットの情報、伝統的なやり方は更新型が暗黙の前提になっている）
5. ２つの設計アプローチ 追記型（イミュータブル） 発生した事実を追記していく 発生した事実の記録からテーブル設計を考える 更新型（ミュータブル） 発生した事実を使って最新の状態に上書きする 発生した事実の利用からテーブル設計を考える これから説明するのは追記型の考え方とやり方です 更新型とは考え方やり方が異なります 注意 5 （多くの書籍、ネットの情報、伝統的なやり方は更新型が暗黙の前提になっている） クラウド環境の疎結合かつ分散型の処理と 相性が良いデータベース設計スタイル 基本に忠実な設計スタイル
6. 追記型のテーブル設計 中核テーブル 事実を記録するテーブル 変更不可 NOT NULL 周辺テーブル 事実から導き出した二次データ 事実の利用を助ける補助テーブル 性能要件 SELECT文の単純化 理論的には不要 最新状態は事実から導出できる 消失しても復元可能 正しく記録する 消失すると復元不能 6
7. 追記型のテーブル 事実の記録 変更不可 • INSERT オンリー • 事実の改ざん(UPDATE)を禁止 NOT NULL • NULL=不定 • NULLという事実はない 7
8. データベースに記録する内容 プロセス/アクティビティ 受注-出荷-売上-請求-回収 発注-入荷-仕入-支払 商品/製品 施設/機材 要員 顧客 供給者 担当者 価格/料金 割引、与信 オーバーブッキング 規程 計算の根拠 判断基準 活動 約束した事実 履行した事実 資源 利用可能なモノ 増加・減少 当事者 当事者として登録 行動の履歴 8 ４つの領域ごとにテーブル設計のパターンがある（後で説明） ヒト コト モノ キメ
9. 9 事実の記録の考え方
10. 事実とは「過去形」である 起きたこと • いつ何が起きたか 約束したこと • いつ何を約束したか 目論んだこと • いつ何を目論んだか 10 ①データベースに記録するのは、常に過去 ③起きたことの記録→上書き(改ざん)は禁止 ④記録の修正は「赤黒」方式 元の記録＋取消(赤)＋修正後の記録(黒) 3つの記録が残る ②「予定」も「予定した」という過去の記録
11. 事実の記録の利用 ① 事実から計算結果・判断結果を導き出す 注文内容と料金規程から請求金額を算出する 現在の在庫数・入庫予定・出庫予定から出荷可否を判断する ② to do, doing, done の管理 約束したことの履行を促す 履行が滞っていることの検出 ③ 予定・実績・差異の管理 目論んだコトと実際に起きたコトとの差異の検出 11
12. どう記録するか ① 値のグルーピング 例）顧客に関するデータ (顧客番号, 氏名, メールアドレス)の三つの値のグループで記録する ② 値と値の関係（ある値が決まれば他の値は一つに決まる関係） a. 顧客番号がわかれば、氏名とメールアドレスは一つに決まる b. 氏名がわかっても、顧客番号やメールアドレスは一つには決まらない ③ 値のグループの合成（分解と結合） (顧客番号, 氏名)と(顧客番号, メールアドレス)の二つの値グループで記録 [顧客番号]で結合して(顧客番号, 氏名, メールアドレス)のビューを合成する 12
13. テーブル設計の基本 ある値が決まれば、他の値が一つに決まる（一意性） • データを利用するための基本条件 • テーブル設計は、この「識別キー：値」の関係を定義する活動 • 一意性制約（UNIQUE)として記述する 同じ識別キーを使って二つのテーブルの値を合成する(結合性) • さまざまデータを分割して整理する基本 • 外部キー制約(FOREIGN KEY)として記述する 一意性制約と外部キー制約で多様なデータを論理的に整理する 13
14. テーブル設計：４つの視点 ① 識別番号 ② 発生時点 ③ 記録の目的 ④ データの意味 14
15. テーブル設計：① 識別番号 識別番号が同じでも別テーブル（別の値グループ）に分ける 「キーが決まれば値が決まる」一意性は必要条件 ただし「データを論理的に整理する」テーブル設計として十分ではない アンチパターン：識別番号が同じだから同じテーブル 顧客番号に属する値はすべて顧客テーブル 商品番号に属する値はすべて商品テーブル 15
16. テーブル設計：発生時点と記録の目的 ②発生時点 発生時点が異なれば必ず別テーブルに分ける 発生時点が同じでも分けたほうがよいものもある 例：受注時に発生する「出荷先」と「請求先」 ③記録の目的 値の用途が異なれば別のテーブルに分ける 例：届け先／請求先／連絡先 注文番号が同じだが別のテーブルで記録する（用途が異なる） 16
17. テーブル設計：④ データの意味 値のグループに名前がついている 例：届け先住所 (注文番号, 郵便番号, 都道府県, 市区町村, 街区, 番地) 値のグループに名前がついている単位はテーブルの候補 同じデータでも意味が違えば別データ • 顧客の識別情報としての氏名 • 注文の届け先の氏名 • 出荷の連絡先の氏名 同じ値を使うかもしれないが別の意味 顧客氏名を参照するのではなく注文成立時点の値を複製して記録する 17
18. 値と値の関係の整理 キーが決まれば値が決まる キーが同じでも値の意味が異なれば、別のテーブルで記録する a. 顧客登録 (顧客番号, 氏名) b. 連絡方法[必須] (顧客番号, メールアドレス) c. 連絡方法[任意] (顧客番号, 電話番号) こうすべきとは限らないが、 こういう設計の選択肢があることを知っておく 18
19. 19 どう記録するか
20. どう記録するか ① 意味を明確にする • スキーマ名 • テーブル名 • カラム名 ② 正確に記録する • データ型 • 制約 ③ テーブルとテーブルを関係づける • 先行-後続 • 集約-明細 • 一意-重複 20
21. アンチパターン：いいかげんな記録 • 名前がいいかげん（意味不明な名前、虚偽の名前） • データ型がいいかげん • 制約がいいかげん • NOT NULL 制約がない • UNIQUE 制約がない • FOREIGN KEY 制約がない • CHECK 制約がない 21
22. 22 テーブル設計のやり方/学び方
23. 設計の準備 実際に手を動かす準備 データベース (PostgreSQL) クライアントツール（IntelliJ IDEA Database Tool) SQL文の実行 テーブル定義/データ内容のブラウジング テーブル設計の可視化 CREATE TABLE 文 (schema.sql) INSERT 文 (data.sql) 23 PostgreSQL推奨 データ型と制約の学習 追記方式の学習 テキストで記述して実行する （テーブル設計の文書化）
24. アンチパターン • ツールを使ってDDL文を自動生成 • フレームワークでDDL文の作成と実行を隠蔽 • DDL/SQL書けなくてもデータベースは使える 24
25. 25 正しく記録する
26. CREATE TABLE文を理解する CREATE SCHEMA スキーマ名; CREATE TABLE スキーマ名.テーブル名 ( 列名 データ型 PRIMARY KEY, 列名 データ型 NOT NULL UNIQUE, 列名 データ型 NOT NULL REFRENCES スキーマ名.テーブル名(列名), 列名 データ型 NOT NULL CHECK ( 条件式 ) _created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); ＋複数カラムのPRIMARY KEY 制約/UNIQUE 制約/FOREIGN KEY制約の書き方 26
27. スキーマ名・テーブル名・列名にこだわる 日本語 説明的な名前(SQL文自体を設計ドキュメントにする） スキーマによる構造化 テーブルをグルーピング スキーマ名（グループ名）で関心事を分離する 関連づけテーブルの名前 a. 部門_X_従業員：構造の表現を重視 b. 所属：意味を表現かつ更新型 c. 配属_履歴：意味を表現かつ追記型 27
28. データ型：可能な限り狭く定義する 数量 NUMERIC(有効桁数, 小数点以下の桁数）を使う 個数 自然数（０を含まない）という選択肢、下限と上限 日付 日付のみの型を選ぶ（文字列にしない） その他の検討事項 • 識別番号 数値 or 文字列 or UUID • 名称 文字列、最大長（２０, ４０，１００，２００, … ) • 作成タイムスタンプ UTC（監査列、アプリケーションでは利用しない） CHECK 制約でデータ型をさらに制限する（有効な値を厳密に定義する） 28
29. 制約にこだわる：良いテーブル設計の根幹 設計意図の表現手段 制約の意味を理解して使う 積極的に制約を宣言する ① NOT NULL ② 一意性制約(UNIQUE) ③ 主キー制約(PRIMARY KEY) ④ 外部キー制約(FOREIGN KEY) ⑤ チェック制約（データ型の強化：設計意図の表現） 29
30. 制約：① NOT NULL すべての列で宣言すべき制約 NULLという事実はない NULLはSQLとプログラムに不要な複雑さを持ち込む 30
31. 制約：② 一意性制約 (UNIQUE) 列あるいは列のグループに含まれるデータが、テーブル内の全ての行 で一意であることを確実する手段 一意にならない（値が重複）に違和感を持とう！ • できる設計者とできない設計者の分岐点 • すべての列/列の組み合わせで一意性の候補を探し宣言する • 実データで一意性制約を宣言できない列/列グループで失敗の理由を調べる • 一意の値のテーブルを作って、そのテーブルへの外部キー制約を宣言する 31
32. 制約：③ 主キー制約(PRIMARY KEY) 実体は「一意性制約+NOT NULL制約」 各テーブルに一つだけという点が特別 複数の一意性制約（候補キー）から何を「主」とするか選択する • そのテーブルの主たる識別キーは何かを設計の意図の表明 • 主キーを決める前に、候補キーを洗い出すことが重要 32
33. 制約：④ 外部キー制約(FOREIGN KEY) そのテーブル以外のテーブルの「一意キー」への参照 テーブルとテーブルの関係を記述する唯一の手段 • 値が「重複のない有限集合の要素」であることの表現 • 有効な値を重複のない有限集合として宣言する • 先行・後続の関係を表現 • ヘッダー・明細の関係を表現 • １対１関係：２テーブルの主キーが同じ(主キーかつ外部キー) 33
34. テーブル間の関係を外部キー制約で表現 34 1 0..1 1 0..n 1 0..n 0..n 1 1対１の関係 1対多の関係 1対多＋1対多 の組み合わせ どれかのパターンになる 最小１であることは制約では宣言できない 同じ主キー 右側の主キーに左側の主キーへの 外部キー制約を宣言する 交差テーブル
35. 制約：⑤ 検査制約(CHECK) 有効な値（無効な値）の定義 データ型の制限をさらに強化する 有効な値を保証することで、SQLやプログラムを単純にする どこまで宣言するか？ • 基本はすべて書くこと（有効な値を定義し、かつ文書化する） • エクセルのテーブル定義書ではなくコード(DDL文)で文書化しよう！ 35
36. 対象別のテーブル設計の基礎 36 活動・資源・当事者・規程
37. 「活動」を扱うテーブル設計のパターン 37 中核4テーブル 約束が発生した 着手した 完了した 約束内容 周辺2テーブル _着手待ち _完了待ち to do リスト INSERT/DELETE to do リスト INSERT/DELETE INSERT INSERT INSERT INSERT 関心事 約束の発生 履行の促進 不履行の検出 約束の複合＝契約 例） 出荷の約束＆支払の約束
38. 「資源」を扱うテーブル設計のパターン 38 中核テーブル 減少した 増加予定 管理対象 周辺テーブル _最新残高 任意（導出可能） INSERT INSERT INSERT INSERT INSERT 関心事 利用可否の判断 納期回答 増加した 減少予定 確定残高 残高履歴 任意（導出可能） INSERT/DELETE 最後の残高履歴への参照 または 最後の残高履歴の複製 _想定残高 任意（導出可能） 将来の利用可否：未来在庫、施設予約、要員手配、…
39. 「当事者」を扱うテーブル設計のパターン 39 中核テーブル 連絡先 連絡履歴 取引履歴 当事者の識別 INSERT INSERT INSERT INSERT 関心事 意思の疎通 約束の複合＝契約 例） 出荷の約束＆支払の約束 事案 事案の発生 事案の終了 対応履歴 意向 INSERT INSERT INSERT/DELETE 与信 評価
40. 「規程」を扱うテーブル設計 判断基準や計算のパラメータ 方針の選択 ① できるだけテーブルで ② できるだけプログラムで 40 ⚫ 区分 ⚫ 価格/料金 ⚫ 割引率 ⚫ 数量制限/人数制限 ⚫ 期限/日数 ⚫ オーバーブッキング ⚫ キャンセル規程 例） プログラム変更とデプロイがたいへんだった時代は テーブル記述が基本だった 常時プログラムを変更しデプロイできるようになった時 どうするのが費用対効果が高いか？
41. 補足：商品の説明・仕様・分類 どこまで構造化するか/できるか 同じ構造で扱える情報と扱えない情報を見極める すべての商品を同じ構造で扱える場合 • テーブル設計は比較的容易 同じ構造で扱いにくい場合（候補） • 構造別のテーブルに分ける（テーブル設計スキルアップのよい材料） • TextやJSONで扱う（動かすことはできるが…) 41
42. 42 参考情報
43. 43 PostgreSQL文書 5. データ定義 8. データ型 第1章 SQLとリレーショナルモデル 第7章 NULLとの戦い 第2章 データベース設計の基礎知識 付録：SQLのからくり 第6章 データベースの設計と ドメインオブジェクト