Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

増田 亨

March 30, 2022
Tweet

More Decks by 増田 亨

Other Decks in Programming

Transcript

  1. 追記型のテーブル設計 中核テーブル 事実を記録するテーブル 変更不可 NOT NULL 周辺テーブル 事実から導き出した二次データ 事実の利用を助ける補助テーブル 性能要件

    SELECT文の単純化 理論的には不要 最新状態は事実から導出できる 消失しても復元可能 正しく記録する 消失すると復元不能 6
  2. データベースに記録する内容 プロセス/アクティビティ 受注-出荷-売上-請求-回収 発注-入荷-仕入-支払 商品/製品 施設/機材 要員 顧客 供給者 担当者

    価格/料金 割引、与信 オーバーブッキング 規程 計算の根拠 判断基準 活動 約束した事実 履行した事実 資源 利用可能なモノ 増加・減少 当事者 当事者として登録 行動の履歴 8 4つの領域ごとにテーブル設計のパターンがある(後で説明) ヒト コト モノ キメ
  3. 事実とは「過去形」である 起きたこと • いつ何が起きたか 約束したこと • いつ何を約束したか 目論んだこと • いつ何を目論んだか

    10 ①データベースに記録するのは、常に過去 ③起きたことの記録→上書き(改ざん)は禁止 ④記録の修正は「赤黒」方式 元の記録+取消(赤)+修正後の記録(黒) 3つの記録が残る ②「予定」も「予定した」という過去の記録
  4. 事実の記録の利用 ① 事実から計算結果・判断結果を導き出す 注文内容と料金規程から請求金額を算出する 現在の在庫数・入庫予定・出庫予定から出荷可否を判断する ② to do, doing, done

    の管理 約束したことの履行を促す 履行が滞っていることの検出 ③ 予定・実績・差異の管理 目論んだコトと実際に起きたコトとの差異の検出 11
  5. どう記録するか ① 値のグルーピング 例)顧客に関するデータ (顧客番号, 氏名, メールアドレス)の三つの値のグループで記録する ② 値と値の関係(ある値が決まれば他の値は一つに決まる関係) a.

    顧客番号がわかれば、氏名とメールアドレスは一つに決まる b. 氏名がわかっても、顧客番号やメールアドレスは一つには決まらない ③ 値のグループの合成(分解と結合) (顧客番号, 氏名)と(顧客番号, メールアドレス)の二つの値グループで記録 [顧客番号]で結合して(顧客番号, 氏名, メールアドレス)のビューを合成する 12
  6. テーブル設計:④ データの意味 値のグループに名前がついている 例:届け先住所 (注文番号, 郵便番号, 都道府県, 市区町村, 街区, 番地)

    値のグループに名前がついている単位はテーブルの候補 同じデータでも意味が違えば別データ • 顧客の識別情報としての氏名 • 注文の届け先の氏名 • 出荷の連絡先の氏名 同じ値を使うかもしれないが別の意味 顧客氏名を参照するのではなく注文成立時点の値を複製して記録する 17
  7. 値と値の関係の整理 キーが決まれば値が決まる キーが同じでも値の意味が異なれば、別のテーブルで記録する a. 顧客登録 (顧客番号, 氏名) b. 連絡方法[必須] (顧客番号,

    メールアドレス) c. 連絡方法[任意] (顧客番号, 電話番号) こうすべきとは限らないが、 こういう設計の選択肢があることを知っておく 18
  8. どう記録するか ① 意味を明確にする • スキーマ名 • テーブル名 • カラム名 ②

    正確に記録する • データ型 • 制約 ③ テーブルとテーブルを関係づける • 先行-後続 • 集約-明細 • 一意-重複 20
  9. 設計の準備 実際に手を動かす準備 データベース (PostgreSQL) クライアントツール(IntelliJ IDEA Database Tool) SQL文の実行 テーブル定義/データ内容のブラウジング

    テーブル設計の可視化 CREATE TABLE 文 (schema.sql) INSERT 文 (data.sql) 23 PostgreSQL推奨 データ型と制約の学習 追記方式の学習 テキストで記述して実行する (テーブル設計の文書化)
  10. 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
  11. データ型:可能な限り狭く定義する 数量 NUMERIC(有効桁数, 小数点以下の桁数)を使う 個数 自然数(0を含まない)という選択肢、下限と上限 日付 日付のみの型を選ぶ(文字列にしない) その他の検討事項 •

    識別番号 数値 or 文字列 or UUID • 名称 文字列、最大長(20, 40,100,200, … ) • 作成タイムスタンプ UTC(監査列、アプリケーションでは利用しない) CHECK 制約でデータ型をさらに制限する(有効な値を厳密に定義する) 28
  12. 制約にこだわる:良いテーブル設計の根幹 設計意図の表現手段 制約の意味を理解して使う 積極的に制約を宣言する ① NOT NULL ② 一意性制約(UNIQUE) ③

    主キー制約(PRIMARY KEY) ④ 外部キー制約(FOREIGN KEY) ⑤ チェック制約(データ型の強化:設計意図の表現) 29
  13. テーブル間の関係を外部キー制約で表現 34 1 0..1 1 0..n 1 0..n 0..n 1

    1対1の関係 1対多の関係 1対多+1対多 の組み合わせ どれかのパターンになる 最小1であることは制約では宣言できない 同じ主キー 右側の主キーに左側の主キーへの 外部キー制約を宣言する 交差テーブル
  14. 「活動」を扱うテーブル設計のパターン 37 中核4テーブル 約束が発生した 着手した 完了した 約束内容 周辺2テーブル _着手待ち _完了待ち

    to do リスト INSERT/DELETE to do リスト INSERT/DELETE INSERT INSERT INSERT INSERT 関心事 約束の発生 履行の促進 不履行の検出 約束の複合=契約 例) 出荷の約束&支払の約束
  15. 「資源」を扱うテーブル設計のパターン 38 中核テーブル 減少した 増加予定 管理対象 周辺テーブル _最新残高 任意(導出可能) INSERT

    INSERT INSERT INSERT INSERT 関心事 利用可否の判断 納期回答 増加した 減少予定 確定残高 残高履歴 任意(導出可能) INSERT/DELETE 最後の残高履歴への参照 または 最後の残高履歴の複製 _想定残高 任意(導出可能) 将来の利用可否:未来在庫、施設予約、要員手配、…
  16. 「当事者」を扱うテーブル設計のパターン 39 中核テーブル 連絡先 連絡履歴 取引履歴 当事者の識別 INSERT INSERT INSERT

    INSERT 関心事 意思の疎通 約束の複合=契約 例) 出荷の約束&支払の約束 事案 事案の発生 事案の終了 対応履歴 意向 INSERT INSERT INSERT/DELETE 与信 評価
  17. 「規程」を扱うテーブル設計 判断基準や計算のパラメータ 方針の選択 ① できるだけテーブルで ② できるだけプログラムで 40 ⚫ 区分

    ⚫ 価格/料金 ⚫ 割引率 ⚫ 数量制限/人数制限 ⚫ 期限/日数 ⚫ オーバーブッキング ⚫ キャンセル規程 例) プログラム変更とデプロイがたいへんだった時代は テーブル記述が基本だった 常時プログラムを変更しデプロイできるようになった時 どうするのが費用対効果が高いか?
  18. 43 PostgreSQL文書 5. データ定義 8. データ型 第1章 SQLとリレーショナルモデル 第7章 NULLとの戦い

    第2章 データベース設計の基礎知識 付録:SQLのからくり 第6章 データベースの設計と ドメインオブジェクト