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

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

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

Transcript

1. テーブル設計の考え方とやり方
   【入門編】
   有限会社システム設計 増田 亨
   現場から学ぶモデル駆動設計 第１４回
   基本から学ぶテーブル設計 超入門！
   

   View Slide

2. お話しする内容
   ① データベースを利用する目的
   事実の記録・事実の利用
   ② ２つの設計アプローチ
   追記型と更新型
   ③ 何を記録するのか
   活動・資源・規程・当事者
   ④ テーブル設計のやり方/学び方
   CREATE TABLE 文、名前・データ型・制約
   ⑤ テーブル設計の基本パターン
   2
   

   View Slide

3. データベースを利用する目的
   ① 発生した事実を記録する
   ② 記録した事実を利用する
   3
   

   View Slide

4. ２つの設計アプローチ
   追記型（イミュータブル）
   発生した事実を追記していく
   発生した事実の記録からテーブル設計を考える
   更新型（ミュータブル）
   発生した事実を使って最新の状態に上書きする
   発生した事実の利用からテーブル設計を考える
   これから説明するのは追記型の考え方とやり方です
   更新型とは考え方やり方が異なります
   注意
   4
   （多くの書籍、ネットの情報、伝統的なやり方は更新型が暗黙の前提になっている）
   

   View Slide

5. ２つの設計アプローチ
   追記型（イミュータブル）
   発生した事実を追記していく
   発生した事実の記録からテーブル設計を考える
   更新型（ミュータブル）
   発生した事実を使って最新の状態に上書きする
   発生した事実の利用からテーブル設計を考える
   これから説明するのは追記型の考え方とやり方です
   更新型とは考え方やり方が異なります
   注意
   5
   （多くの書籍、ネットの情報、伝統的なやり方は更新型が暗黙の前提になっている）
   クラウド環境の疎結合かつ分散型の処理と
   相性が良いデータベース設計スタイル
   基本に忠実な設計スタイル
   

   View Slide

6. 追記型のテーブル設計
   中核テーブル
   事実を記録するテーブル
   変更不可
   NOT NULL
   周辺テーブル
   事実から導き出した二次データ
   事実の利用を助ける補助テーブル
   性能要件
   SELECT文の単純化
   理論的には不要
   最新状態は事実から導出できる
   消失しても復元可能
   正しく記録する
   消失すると復元不能
   6
   

   View Slide

7. 追記型のテーブル
   事実の記録
   変更不可
   • INSERT オンリー
   • 事実の改ざん(UPDATE)を禁止
   NOT NULL
   • NULL=不定
   • NULLという事実はない
   7
   

   View Slide

8. データベースに記録する内容
   プロセス/アクティビティ
   受注-出荷-売上-請求-回収
   発注-入荷-仕入-支払
   商品/製品
   施設/機材
   要員
   顧客
   供給者
   担当者
   価格/料金
   割引、与信
   オーバーブッキング
   規程
   計算の根拠
   判断基準
   活動
   約束した事実
   履行した事実
   資源
   利用可能なモノ
   増加・減少
   当事者
   当事者として登録
   行動の履歴
   8
   ４つの領域ごとにテーブル設計のパターンがある（後で説明）
   ヒト
   コト
   モノ
   キメ
   

   View Slide

9. 9
   事実の記録の考え方
   

   View Slide

10. 事実とは「過去形」である
    起きたこと
    • いつ何が起きたか
    約束したこと
    • いつ何を約束したか
    目論んだこと
    • いつ何を目論んだか
    10
    ①データベースに記録するのは、常に過去
    ③起きたことの記録→上書き(改ざん)は禁止
    ④記録の修正は「赤黒」方式
    元の記録＋取消(赤)＋修正後の記録(黒)
    3つの記録が残る
    ②「予定」も「予定した」という過去の記録
    

    View Slide

11. 事実の記録の利用
    ① 事実から計算結果・判断結果を導き出す
    注文内容と料金規程から請求金額を算出する
    現在の在庫数・入庫予定・出庫予定から出荷可否を判断する
    ② to do, doing, done の管理
    約束したことの履行を促す
    履行が滞っていることの検出
    ③ 予定・実績・差異の管理
    目論んだコトと実際に起きたコトとの差異の検出
    11
    

    View Slide

12. どう記録するか
    ① 値のグルーピング
    例）顧客に関するデータ
    (顧客番号, 氏名, メールアドレス)の三つの値のグループで記録する
    ② 値と値の関係（ある値が決まれば他の値は一つに決まる関係）
    a. 顧客番号がわかれば、氏名とメールアドレスは一つに決まる
    b. 氏名がわかっても、顧客番号やメールアドレスは一つには決まらない
    ③ 値のグループの合成（分解と結合）
    (顧客番号, 氏名)と(顧客番号, メールアドレス)の二つの値グループで記録
    [顧客番号]で結合して(顧客番号, 氏名, メールアドレス)のビューを合成する
    12
    

    View Slide

13. テーブル設計の基本
    ある値が決まれば、他の値が一つに決まる（一意性）
    • データを利用するための基本条件
    • テーブル設計は、この「識別キー：値」の関係を定義する活動
    • 一意性制約（UNIQUE)として記述する
    同じ識別キーを使って二つのテーブルの値を合成する(結合性)
    • さまざまデータを分割して整理する基本
    • 外部キー制約(FOREIGN KEY)として記述する
    一意性制約と外部キー制約で多様なデータを論理的に整理する
    13
    

    View Slide

14. テーブル設計：４つの視点
    ① 識別番号
    ② 発生時点
    ③ 記録の目的
    ④ データの意味
    14
    

    View Slide

15. テーブル設計：① 識別番号
    識別番号が同じでも別テーブル（別の値グループ）に分ける
    「キーが決まれば値が決まる」一意性は必要条件
    ただし「データを論理的に整理する」テーブル設計として十分ではない
    アンチパターン：識別番号が同じだから同じテーブル
    顧客番号に属する値はすべて顧客テーブル
    商品番号に属する値はすべて商品テーブル
    15
    

    View Slide

16. テーブル設計：発生時点と記録の目的
    ②発生時点
    発生時点が異なれば必ず別テーブルに分ける
    発生時点が同じでも分けたほうがよいものもある
    例：受注時に発生する「出荷先」と「請求先」
    ③記録の目的
    値の用途が異なれば別のテーブルに分ける
    例：届け先／請求先／連絡先
    注文番号が同じだが別のテーブルで記録する（用途が異なる）
    16
    

    View Slide

17. テーブル設計：④ データの意味
    値のグループに名前がついている
    例：届け先住所 (注文番号, 郵便番号, 都道府県, 市区町村, 街区, 番地)
    値のグループに名前がついている単位はテーブルの候補
    同じデータでも意味が違えば別データ
    • 顧客の識別情報としての氏名
    • 注文の届け先の氏名
    • 出荷の連絡先の氏名
    同じ値を使うかもしれないが別の意味
    顧客氏名を参照するのではなく注文成立時点の値を複製して記録する
    17
    

    View Slide

18. 値と値の関係の整理
    キーが決まれば値が決まる
    キーが同じでも値の意味が異なれば、別のテーブルで記録する
    a. 顧客登録 (顧客番号, 氏名)
    b. 連絡方法[必須] (顧客番号, メールアドレス)
    c. 連絡方法[任意] (顧客番号, 電話番号)
    こうすべきとは限らないが、
    こういう設計の選択肢があることを知っておく
    18
    

    View Slide

19. 19
    どう記録するか
    

    View Slide

20. どう記録するか
    ① 意味を明確にする
    • スキーマ名
    • テーブル名
    • カラム名
    ② 正確に記録する
    • データ型
    • 制約
    ③ テーブルとテーブルを関係づける
    • 先行-後続
    • 集約-明細
    • 一意-重複
    20
    

    View Slide

21. アンチパターン：いいかげんな記録
    • 名前がいいかげん（意味不明な名前、虚偽の名前）
    • データ型がいいかげん
    • 制約がいいかげん
    • NOT NULL 制約がない
    • UNIQUE 制約がない
    • FOREIGN KEY 制約がない
    • CHECK 制約がない
    21
    

    View Slide

22. 22
    テーブル設計のやり方/学び方
    

    View Slide

23. 設計の準備
    実際に手を動かす準備
    データベース (PostgreSQL)
    クライアントツール（IntelliJ IDEA Database Tool)
    SQL文の実行
    テーブル定義/データ内容のブラウジング
    テーブル設計の可視化
    CREATE TABLE 文 (schema.sql)
    INSERT 文 (data.sql)
    23
    PostgreSQL推奨
    データ型と制約の学習
    追記方式の学習
    テキストで記述して実行する
    （テーブル設計の文書化）
    

    View Slide

24. アンチパターン
    • ツールを使ってDDL文を自動生成
    • フレームワークでDDL文の作成と実行を隠蔽
    • DDL/SQL書けなくてもデータベースは使える
    24
    

    View Slide

25. 25
    正しく記録する
    

    View Slide

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
    

    View Slide

27. スキーマ名・テーブル名・列名にこだわる
    日本語
    説明的な名前(SQL文自体を設計ドキュメントにする）
    スキーマによる構造化
    テーブルをグルーピング
    スキーマ名（グループ名）で関心事を分離する
    関連づけテーブルの名前
    a. 部門_X_従業員：構造の表現を重視
    b. 所属：意味を表現かつ更新型
    c. 配属_履歴：意味を表現かつ追記型
    27
    

    View Slide

28. データ型：可能な限り狭く定義する
    数量 NUMERIC(有効桁数, 小数点以下の桁数）を使う
    個数 自然数（０を含まない）という選択肢、下限と上限
    日付 日付のみの型を選ぶ（文字列にしない）
    その他の検討事項
    • 識別番号 数値 or 文字列 or UUID
    • 名称 文字列、最大長（２０, ４０，１００，２００, … )
    • 作成タイムスタンプ UTC（監査列、アプリケーションでは利用しない）
    CHECK 制約でデータ型をさらに制限する（有効な値を厳密に定義する）
    28
    

    View Slide

29. 制約にこだわる：良いテーブル設計の根幹
    設計意図の表現手段
    制約の意味を理解して使う
    積極的に制約を宣言する
    ① NOT NULL
    ② 一意性制約(UNIQUE)
    ③ 主キー制約(PRIMARY KEY)
    ④ 外部キー制約(FOREIGN KEY)
    ⑤ チェック制約（データ型の強化：設計意図の表現）
    29
    

    View Slide

30. 制約：① NOT NULL
    すべての列で宣言すべき制約
    NULLという事実はない
    NULLはSQLとプログラムに不要な複雑さを持ち込む
    30
    

    View Slide

31. 制約：② 一意性制約 (UNIQUE)
    列あるいは列のグループに含まれるデータが、テーブル内の全ての行
    で一意であることを確実する手段
    一意にならない（値が重複）に違和感を持とう！
    • できる設計者とできない設計者の分岐点
    • すべての列/列の組み合わせで一意性の候補を探し宣言する
    • 実データで一意性制約を宣言できない列/列グループで失敗の理由を調べる
    • 一意の値のテーブルを作って、そのテーブルへの外部キー制約を宣言する
    31
    

    View Slide

32. 制約：③ 主キー制約(PRIMARY KEY)
    実体は「一意性制約+NOT NULL制約」
    各テーブルに一つだけという点が特別
    複数の一意性制約（候補キー）から何を「主」とするか選択する
    • そのテーブルの主たる識別キーは何かを設計の意図の表明
    • 主キーを決める前に、候補キーを洗い出すことが重要
    32
    

    View Slide

33. 制約：④ 外部キー制約(FOREIGN KEY)
    そのテーブル以外のテーブルの「一意キー」への参照
    テーブルとテーブルの関係を記述する唯一の手段
    • 値が「重複のない有限集合の要素」であることの表現
    • 有効な値を重複のない有限集合として宣言する
    • 先行・後続の関係を表現
    • ヘッダー・明細の関係を表現
    • １対１関係：２テーブルの主キーが同じ(主キーかつ外部キー)
    33
    

    View Slide

34. テーブル間の関係を外部キー制約で表現
    34
    1 0..1
    1 0..n
    1 0..n 0..n 1
    1対１の関係
    1対多の関係
    1対多＋1対多
    の組み合わせ
    どれかのパターンになる
    最小１であることは制約では宣言できない
    同じ主キー
    右側の主キーに左側の主キーへの
    外部キー制約を宣言する
    交差テーブル
    

    View Slide

35. 制約：⑤ 検査制約(CHECK)
    有効な値（無効な値）の定義
    データ型の制限をさらに強化する
    有効な値を保証することで、SQLやプログラムを単純にする
    どこまで宣言するか？
    • 基本はすべて書くこと（有効な値を定義し、かつ文書化する）
    • エクセルのテーブル定義書ではなくコード(DDL文)で文書化しよう！
    35
    

    View Slide

36. 対象別のテーブル設計の基礎
    36
    活動・資源・当事者・規程
    

    View Slide

37. 「活動」を扱うテーブル設計のパターン
    37
    中核4テーブル
    約束が発生した
    着手した
    完了した
    約束内容
    周辺2テーブル
    _着手待ち
    _完了待ち
    to do リスト
    INSERT/DELETE
    to do リスト
    INSERT/DELETE
    INSERT
    INSERT
    INSERT
    INSERT
    関心事
    約束の発生
    履行の促進
    不履行の検出
    約束の複合＝契約 例） 出荷の約束＆支払の約束
    

    View Slide

38. 「資源」を扱うテーブル設計のパターン
    38
    中核テーブル
    減少した
    増加予定
    管理対象
    周辺テーブル
    _最新残高
    任意（導出可能）
    INSERT
    INSERT
    INSERT
    INSERT
    INSERT
    関心事
    利用可否の判断
    納期回答
    増加した
    減少予定
    確定残高
    残高履歴
    任意（導出可能）
    INSERT/DELETE
    最後の残高履歴への参照
    または
    最後の残高履歴の複製
    _想定残高 任意（導出可能）
    将来の利用可否：未来在庫、施設予約、要員手配、…
    

    View Slide

39. 「当事者」を扱うテーブル設計のパターン
    39
    中核テーブル
    連絡先
    連絡履歴
    取引履歴
    当事者の識別
    INSERT
    INSERT INSERT
    INSERT
    関心事
    意思の疎通
    約束の複合＝契約 例） 出荷の約束＆支払の約束
    事案
    事案の発生
    事案の終了
    対応履歴
    意向
    INSERT
    INSERT
    INSERT/DELETE
    与信
    評価
    

    View Slide

40. 「規程」を扱うテーブル設計
    判断基準や計算のパラメータ
    方針の選択
    ① できるだけテーブルで
    ② できるだけプログラムで
    40
    ⚫ 区分
    ⚫ 価格/料金
    ⚫ 割引率
    ⚫ 数量制限/人数制限
    ⚫ 期限/日数
    ⚫ オーバーブッキング
    ⚫ キャンセル規程
    例）
    プログラム変更とデプロイがたいへんだった時代は
    テーブル記述が基本だった
    常時プログラムを変更しデプロイできるようになった時
    どうするのが費用対効果が高いか？
    

    View Slide

41. 補足：商品の説明・仕様・分類
    どこまで構造化するか/できるか
    同じ構造で扱える情報と扱えない情報を見極める
    すべての商品を同じ構造で扱える場合
    • テーブル設計は比較的容易
    同じ構造で扱いにくい場合（候補）
    • 構造別のテーブルに分ける（テーブル設計スキルアップのよい材料）
    • TextやJSONで扱う（動かすことはできるが…)
    41
    

    View Slide

42. 42
    参考情報
    

    View Slide

43. 43
    PostgreSQL文書
    5. データ定義
    8. データ型
    第1章
    SQLとリレーショナルモデル
    第7章
    NULLとの戦い
    第2章
    データベース設計の基礎知識
    付録：SQLのからくり
    第6章
    データベースの設計と
    ドメインオブジェクト
    

    View Slide