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

by 増田亨

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

9 事実の記録の考え方

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

19 どう記録するか

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

25 正しく記録する

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

42 参考情報

Slide 43

Slide 43 text

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