データモデリング テクニック 伊津野 英克 [email protected] Version 1.03

はじめに データモデリングとは ⚫データ構造を整理する技法 ⚫通常、リレーショナル・データベースにおけるエンティティ 設計法のことを指す ⇒ER（エンティティ・リレーション）分析 • エンティティ：データ集合を表現する概念 • リレーション：エンティティ間の関係 ⚫リレーショナル以外のデータベースの設計手法は 体系化されていない ・階層型データベース（IMS） ・ネットワーク型データベース（CODASYL） ・KVS（LevelDB） 2

なぜデータモデリングが必要か ⚫データベースはグローバル変数 ⚫どのプログラムからでも更新・参照できる ⚫管理しないとぐちゃぐちゃに ⚫整理されてないデータはただのゴミ ⚫Garbage in, garbage out ⚫使うのも、更新するのも難しい ⚫適切にデータモデリングすると ⚫データの重複がなくなる ⚫データの不整合がなくなる ⚫データへのアクセス方法が明確になる 3

いろいろなデータモデリング手法 標準的なモデリング手法が存在するわけではない ⚫Peter Chen記法：ER図の元祖（だが使われない） ⚫THモデル：椿正明／穂高良介 ⚫IE (Information Engineering) 記法 ⚫IDEF1X (Integration Definition) 記法 ⚫T字型ER：佐藤正美 ⚫三要素分析法：渡辺幸三 ⚫UMLクラス図：オブジェクト指向設計 4 記法や詳細は異なるが、基本は通底している それぞれの方法論は必ずしも排他的ではなく、学べることも多い

IE記法 どの記法を使うべきか ⚫IDEF1X か IE記法（鳥足）が一般的 ⚫今となっては「UMLクラス図」が一番いいかも ⚫ 誰でも知ってるし、わかりやすい ⚫ 機能が多いことより、みんながわかる／知ってることが重要 ⚫ この資料ではUMLクラス図を使うことにする ⚫ 欠点：主キーの表現がいまいち書きづらい B C A D IDEF1X B C A D Z P UMLクラス図 B C A D 5 1 0..1 1 0..* 1 1..* Aひとつに対し、Bは0～1個、Cは0個以上、Dは1個以上のデータが紐づくことを表現している この関係の数のことをカーディナリティ（多重度）と呼ぶ。

データモデルの種類 概念⇒論理⇒物理の順に詳細化 ⚫概念モデル：概念と概念間の関係を整理したもの ⚫論理モデル：エンティティと属性、エンティティ間の関係を整理したもの ⚫物理モデル：データベース内のテーブル、カラム、外部キーなどの定義 6 概念モデル 売上 得意先 商品 0..* 1 1 0..* 論理モデル 売上 売上番号 得意先コード 得意先 得意先コード 得意先名称 売上明細 売上番号 明細番号 商品コード 売上金額 商品 商品コード 商品名称 0..* 1 1 1..* 1 0..* 物理モデル sales sale_no customer_code customers customer_code customer_name sale_details sale_no sale_detail_no Item_code sale_amount items item_code item_name 0..* 1 1 1..* 1 0..* ※なお、これ以降スペースの関係で主キーは太字、外部キーは下線で表現している。 UMLの正しい表現としてはステレオタイプを用い<>などと記載するのが一般的。

論理モデル 物理モデル 説明 エンティティ テーブル データのまとまりのこと 属性 列(Column) データ項目のこと タプル 行(Row) データ項目のまとまりのこと リレーション － データ間の関係性のこと（※） 主キー 最も有力な候補キー 候補キー － 冗長性のないスーパーキー スーパーキー － データを一意に特定可能な属性の集合 自然キー － 業務で実際に使われる候補キー 代替キー － 自然キーの代わりに人工的に作られた候補キー 外部キー 別のデータと紐付け可能な属性の集合 データモデリングで使う用語 ⚫論理モデルと物理モデルで用語が違うものもある ⚫ エンティティが抽象概念でテーブルはその実装 ⚫ 用語だけの話なのであまり気にしなくていい 7 ※RDBの基礎となるリレーショナル・モデルでのリレーション（関係）とは、タプルの値の組み合わせ、 すなわちテーブルに相当する概念を意味する。

論理／物理の住み分け ⚫論理モデルと物理モデルは違うものではあるが…… ⚫フェイズが進むにつれて、論理と物理のふたつの設計書 の整合性をとるのはどんどん困難に ⚫メンテするER図は概念レベルにとどめて、 論理/物理モデルはエンティティ定義書で管理 ⚫エンティティ数が増えると、ER図も大きくなりすぎて 一覧性が低くなる ⚫実装が進むと、物理モデルの方が重要になってくる 8 論理ER図は設計用と割り切る

エンティティ設計の手順 1. イベント系エンティティを抽出する 2. イベント系エンティティを正規化する 3. リソース系エンティティを統廃合する 4. リレーションを見直す 5. 仮想エンティティを探し出す 6. ドメインによる項目の正規化 7. 物理モデル要件を追加する 9

イベントとリソース エンティティは２種類に分類できる ⚫イベント（≒トランザクション） ⚫ 受注や発注など「ある時刻」に記録されたデータ ⚫ ○○日として表現可能 ⚫ 動詞で表現できる概念はイベント ⚫リソース（≒マスタ） ⚫ 社員や組織など「ある期間」存在するデータ ⚫ 名詞で表現される概念はリソース ⚫ 注意点 ⚫ 「○○を管理する」のような業務はある時刻に発生しないのでイベントにはならない。 業務の粒度が荒すぎるので、もっと深掘りが必要。 ⚫ 「○○を登録する」 「歩く」「息をする」は単なる動作なのでこれもイベントには ならない。あくまで業務のモデリングが目的であることを忘れないようにする。 身近なためリソースからモデリングしがちだが、リソースは一連のイベントの中から再利用 可能な要素を抜き出し識別子を付けたものに過ぎない 10 発注日 仕入先 発注金額 2016/10/22 株式会社ABC 1,000円 2016/10/23 あいう株式会社 1,200円 2016/11/02 株式会社ABC 2,000円 2016/11/02 あいう株式会社 500円 仕入先（リソース） 仕入先ID 仕入先名称 0001 株式会社ABC 0002 あいう株式会社 共通要素を抜き出し、 識別子を付け 再利用可能にする 発注（イベント）

イベント系エンティティの抽出 業務フロー、画面、帳票などからイベントを導出 ⚫見積る、販売する、計画する、請求する…… ⚫イベントの数は限られているので、取りかかりやすい ⚫一般的な社内業務であれば、業務モデリング本も参考になる ⚫ 梅田弘之著「グラス片手にデータベース設計シリーズ」 ⚫ 渡辺幸三著「業務別データベース設計のためのデータモデリング入門」 11 顧客 営業 見積を 依頼する 見積を 作成する 見積を 回答する 注文を 受ける 注文を 依頼する 見積 注文

エンティティ正規化 データの不整合を防ぐための手法 ⚫教科書的な正規化手順 ⚫第１正規形 ⚫第２正規形 ⚫第３正規形 ⚫ボイス・コッド正規形 ⚫第４正規形 ⚫第５正規形 12 第３正規形までは問題なく正規化 できるが、ボイス・コッド正規形 以降は、データの関係性（関数従 属性）が崩れる場合がある 基本情報処理試験でも 扱う内容なので詳細は割愛

エンティティ正規化の意味 教科書的な正規化手順は実践的ではない。正規化で何をやっているのか理 解することが重要。 ⚫One fact in one place（同じデータは一箇所に） ⚫ 同じデータが複数の項目に格納されていると矛盾したデータが登録でき てしまう ⚫従属した項目をひとつにまとめる ⚫ 従属性のある項目はまとめて更新した方が整合性がとりやすい ⚫ 関数従属性： y = f(x) すなわち、y は x だけで決まるということ ⚫ NULL列が多い場合は、関数従属性を見落としている可能性が高い 13 このことがわかっていれば 手順を意識しなくても正規化できる

識別子 ⚫識別子：社員コードなど主キーに使われるコード体系のこと ⚫ そのデータが他のデータとは異なることを示し識別に使用できる ⚫ 正規化したエンティティには必ず識別子を付けそれでアクセスする ⚫ 識別子がないと、内容は同じだが異なるデータにアクセスできない 例：同姓同名、同じ誕生日、同じ出身地の社員 ⚫ 明細や連関エンティティは、複数の識別子の組み合わせが識別子となる ⚫識別子は必ず不変性のある無意コードにする ⚫ 無意なデータに見えても公的に流通しているものは、重複したり変化 する可能性がある ⚫ 氏名：同姓同名、結婚による姓の変更など ⚫ JANコード：使い回し（現代数学がAmazonにて10年前のエロ雑誌として取り扱われる） ⚫ 識別子が変化すると、意図せず関係が変わってしまう ここで言う無意とは「Aで始まるコードは集計対象とする」のように処理の条件には使用しない、という ことを意味している。利用者の利便性のために直観的なコード体系にすること自体は必ずしも悪いこと ではない。 14

イベント系エンティティの正規化 ⚫イベント系エンティティから繰り返しや従属項目をくくりだす 発注日 仕入先 発注番号 明細番号 商品 商品分類 発注金額 2016/10/22 株式会社ABC 00001 1 チョコレート 食品 500円 2016/10/22 株式会社ABC 00001 2 書籍 雑貨 2,000円 2016/10/23 あいう株式会社 00002 1 サンオイル 雑貨 1,200円 2016/11/02 株式会社ABC 00003 1 書籍 雑貨 2,000円 2016/11/02 あいう株式会社 00004 1 チョコレート 食品 500円 2016/11/02 あいう株式会社 00004 2 精肉 食品 800円 発注日 仕入先 コード 発注番号 2016/10/22 001 00001 2016/10/23 002 00002 2016/11/02 001 00003 2016/11/02 002 00004 仕入先コード 仕入先名称 0001 株式会社ABC 0002 あいう株式会社 発注 番号 明細 番号 商品 コード 発注金額 00001 1 0001 500円 00001 2 0002 2,000円 00002 1 0003 1,200円 00003 1 0002 2,000円 00004 1 0001 500円 00004 2 0004 800円 商品 コード 商品名称 商品分 類区分 0001 チョコレート A 0002 書籍 B 0003 サンオイル B 0004 精肉 A 商品分類区分 商品分類名称 A 食品 B 雑貨 15 商品分類は、商品に 従属しているっぽい 発注番号ごとに繰り 返しがあるっぽい 正規化！

余談：過剰な正規化 理屈だけで突き進むとやり過ぎることがある ×都道府県は市区町村に従属しているから取引先マスタから 都道府県を取り除こう！ ⚫ 画面上、市区町村はテキスト項目だが、都道府県は選択項目となる ことが多いので、実装とマッチしない ×請求トランの取引先名称は重複しているので、項目は削除 して、マスタの名称を表示しよう！ ⚫ 請求書などは再出力しても最初の出力時と同じ名称を印字しなけれ ばならないことが多く、出力当時の取引先名称の保持が必要となる ⚫ ファクトデータは捨てると情報量が落ちる場合がある データモデリングの世界は（決定打がないが故に）原理主義に陥りやすいので、 テクニックを参考にしつつ、有用な範囲で導入するのがおすすめ 16

余談：ID方式 ⚫ 主キーはすべて自動連番の内部利用専用IDにすべきという考え方 ⚫ ようはプログラム上のオブジェクトと同様、識別子の具体的な値を意識せ ず、データへの参照だけを扱えばいいという発想 ⚫ 従来でも代替キーという考え方自体はあるが、すべてに適用する点が違う ⚫ Rails の ActiveRecord のような O/Rマッパーと親和性が高いため、開発側が 楽できる（ActiveRecordは標準では複合キー扱えないし） ⚫ リレーションがシンプルになり、識別子の洗い替え問題にも対応できる ⚫ 識別子の洗い替え問題：コード体系を一括で洗い替える 17 個人的な見解としては（特に業務系システムへの導入には）否定的 • 実は論理モデルとは関係なく、物理設計レベルの工夫に過ぎない • 識別子の洗い替えもドメイン管理がきちんとされていればさほど問題ではない • 理屈上は正しいが、主キーに再現性がない状態で、データ比較をしたり、データを チェックするのは実運用上、結構つらい • 業務系だと外部システムとのI/Fが多いため、むしろ面倒が増える 従来方式 売上 売上番号 得意先コード 得意先 得意先コード 得意先名称 0..* 1 ID方式 売上 売上ID 売上番号 得意先ID 得意先 得意先ID 得意先コード 得意先名称 0..* 1

売上 リソース系エンティティの統廃合 ⚫似て非なる概念を統合してしまわないよう注意 ⚫ 商品と製品、得意先と仕入先など ⚫ 必要に応じて上位概念で束ねる（擬似的な継承関係） 18 売上 売上番号 得意先コード 得意先 得意先コード 得意先名称 売上明細 売上番号 明細番号 商品コード 売上金額 商品 商品コード 商品名称 販売価格 発注 発注 発注番号 仕入先コード 仕入先 仕入先コード 仕入先名称 発注明細 発注番号 明細番号 商品コード 発注金額 商品 商品コード 商品名称 仕入価格 商品 商品コード 商品名称 販売価格 仕入価格 ⚫複数のイベントから導出したリソースを統廃合する 統合！ 品目 商品 製品 取引先 仕入先 得意先 分析や売掛金と買掛 金の相殺で使う

リレーションの見直し ⚫一見同じような関係でもモデリングによって異なる機能性を持つ ⚫ 例えば、商品分類と商品のような親子関係にはいくつかのバリエーショ ンが考えられる ⚫ どれが適しているかはデータのライフサイクルに依存する ⚫ 依存性がある場合、依存されるエンティティは、依存するエンティティ より先に生成され、後に削除される必要がある ⚫右図に出てくるエンティティ間の関係を仲介するエンティティを 「連関エンティティ」あるいは「交差エンティティ」と呼ぶ ⚫ 分析/計画用の分類は、後付けとなるためこの方式を使ったほうがよい ⚫ m:n の関係を表現する場合には必須（めったにないけど） 子供（商品）は親（商品分類） のライフサイクルに依存 親（商品分類）は子供（商品） のライフサイクルに依存 親（商品分類）と子供（商品） のライフサイクルは無関係 商品分類 商品分類区分 商品分類名称 商品 商品分類区分 商品コード 商品名称 商品 商品コード 商品分類区分 商品名称 商品分類 商品分類区分 商品分類名称 商品 商品コード 商品名称 商品分類 商品分類区分 商品分類名称 商品分類商品紐付 商品分類区分 商品コード 連関（交差） エンティティ 19

区分のフラグ化 ⚫区分はプログラムの処理分岐に使われるため、増やした場合の影響 範囲が大きくなる ⚫ 区分で処理を分岐するよりも、適用する処理ごとにフラグを分ける 方が汎用的で影響範囲を小さく留めることができる場合がある ⚫ 常にそうすべきというわけではもちろんない 20 IF 区分 IN (‘1’, ‘2’, ‘3’) { 処理Aを実行 } IF 区分 IN (‘3’, ‘5’) { 処理Bを実行 } IF 区分 = ‘4’ { 処理Cを実行 } IF 処理Aフラグ == true { 処理Aを実行 } IF 処理Bフラグ == true { 処理Bを実行 } IF 処理Cフラグ == true { 処理Cを実行 } フラグ化！

余談：履歴ありマスタ ⚫最近は適用期間を持つ履歴ありマスタの利用も一般的になってきた ⚫ 特に組織変更で力を発揮する ⚫ 組織や所属については履歴ありマスタを検討すべき ⚫問題も多いので何でも無条件に履歴ありマスタにするのは考えもの ⚫ 常に範囲検索になるためパフォーマンスが劣化 ⚫ 適用期間外の結合でマスタが欠落（月次データとの結合など） ⚫ 履歴ありマスタ同士を結合した物理ビューを作るのは至難の業 ⚫ DB連動のER図作成ツールと相性が悪い 21 組織 組織コード 適用開始日 適用終了日 上位組織コード 組織名称 社員 社員コード 社員名称 入社日 退社日 所属 組織コード 社員コード 適用開始日 適用終了日 役職

余談：外部キーは付けるべきか 理想的には付けるべきだが…… ⚫マスタの作成など識別子の生成が同一システム内で完結する場合は 付けることが望ましい ⚫ とはいえ、データを取り込む場合、順序に制約が発生し作業が煩雑になる ⚫ 外部キー制約を一時的にオフにできるDBなら問題はないかも （MySQL, PostgreSQL など） ⚫ 外部キーはDB上は索引として実装されるためパフォーマンス上の問題が発 生する恐れも ⚫マスタが外部システムで生成される場合には、不整合を許容せざるを 得ない場合がある ⚫ 外部システムからの連携タイミングによっては不整合が要件 ⚫ 旧システムの整合性が取れていないデータでも移行せざるを得ない 22

余談：T字型ERのNULL排除 ⚫T字型ERでは正規化を更に進め、NULLの存在を許可しない ⚫ NULLだけでなく状態を項目として持つこと自体を許さない ⚫ 状態ごとにテーブルを作り、行のありなしで表現する ⚫ Coddのリレーショナル・モデルにはそもそもNULLという概念がない 23 取引先コード 取引先名称 電話番号 0001 株式会社ABC 090-XXXX-XXXX 0002 あいう株式会社 (NULL) 取引先マスタ 取引先コード 取引先名称 0001 株式会社ABC 0002 あいう株式会社 取引先コード 電話番号 0001 090-XXXX-XXXX 取引先マスタ 取引先電話番号マスタ 分割 考え方自体は有効で役に立つ場面もあるが、エンティティ数が爆発し管理が困難 になるので、必要もないのに適用する必要はない

仮想エンティティの探索 ⚫仮想エンティティ：ようするにビューのこと ⚫別のデータから導出可能なエンティティがある ⚫ 例１：在庫 ⇒ 入庫、出庫から導出可能 ⚫ 例２：残高 ⇒ 仕訳から導出可能 ⇒集計データは仮想エンティティだと思っていい ⚫仮想エンティティは論理設計としては無視できる ⚫ 理屈の上では単なる検索クエリに過ぎない ⚫ パフォーマンスはマテリアライズド・ビューにすれば解決 ⚫ 実際には旧システムから移行した残高データなどがあり、完全 にビュー扱いできない場合もあるが、オンラインからの更新と は独立して処理できるという点が重要 24

非正規化の必要性 ⚫非正規化とは：パフォーマンス改善を目的とした正規化くずし ⚫ 件数が多すぎる場合に結合のコストが無視できない場合というのは 実際にあり、根拠がないというわけではない ⚫ 階層問い合わせの高速化のために、階層をパスに展開し保持するの も非正規化の一例 ⚫非正規化は仮想エンティティで対応すべし ⚫ 非正規化をする＝不整合を許容する、ということ ⚫ 仮想エンティティでなら非正規化状態でも問題ない ⚫ 物理ビューやマテリアライズド・ビュー、バッチ集計とオンタイム 集計を組み合わせたリアルタイム集計ビューを使えば、非正規化の 必要性はなくなる 25 前日分 バッチ集計 当日差分 リアルタイム 集計ビュー オンタイム 集計 リアルタイム集計ビューのイメージ

ドメインによる項目の正規化 エンティティの設計が終わったら次は項目の設計を行う ⚫ドメイン：ここでいうドメインとは、項目定義の種類のことを指す ⚫ ドメインを整理することで、項目を正規化する ⚫ 同じドメイン（＝同じ型、同じ桁数）の項目は同じ名前にする ⚫ 同様に、違うドメインの項目は違う名前になる ⚫ 同種のドメインに属す項目には似たような項目名を付ける ⚫ 入社日、退社日、入庫日、売上日 ⚫ 継承関係にあるドメインは、項目名も継承する ⚫ 組織コード ⇒ 販売組織コード 26 表記のゆれ 正規化ルールの例 ○○名称、○○名 ○○名称に統一 社員、従業員、ユーザー 社員に統一 ID、コード、区分 内部コードにはID、外部コードにはコードを付ける 社員コード 物理名はEMP_CD、型はVARCHAR(10) に統一 項目正規化の例 ドメインだけでなく、項目の並び順にもある程度一貫性を持たせたほうがよい。一般的に、主キーは先頭、 システム共通項目は最後、主要項目ほど前方、といった並びにすることが多い。

物理モデル要件の追加 ⚫更新ユーザ/更新日時などシステム共通項目の追加 ⚫ 業務要件としては必要ないが、運用ではとても重要 ⚫ワークテーブル ⚫ CSVファイルなどを処理用に一旦取り込んだり、処理高速化のため プログラム内で一時的に使うテーブルのこと ⚫ 業務要件ではないので、論理設計では出てこない ⚫インデックス、パーティションキーの設定 ⚫ インデックスは多く付ければ良いというものではない ⚫ 一般的に、検索は速くなるが、更新は遅くなる ⚫ なぜ、前方一致には有効で、後方一致には無効なのか、など仕組みを 理解して使う ⚫ アクセス・パスのショートカットを意識して追加する ⚫ カーディナリティ（値の分散）が低い項目には決して付けない ⚫ 区分やフラグはカーディナリティが低いため、単項目にインデックスを付 けると、むしろパフォーマンスが劣化する ⚫ ややこしいが、ここでのカーディナリティは値の多重度のことでリレー ションのカーディナリティとは意味が異なる 27

論理削除 ⚫論理削除とは ⚫テーブルに「削除フラグ」を設け、falseになっているレ コードを削除したものとみなす仕様 ⚫論理削除は「悪しき習慣」……ではない ⚫論理削除＝削除の代替ではなく無効化 ⚫運用者が誤削除した場合のリカバリを容易にする ⚫業務システムでは物理削除自体が望ましくない ⚫識別子の使い回しを防ぐ簡便な方法でもある とはいえ、物理ビューやワークテーブルにまで削除フラグを付けるのはナ ンセンス。 論理削除を無効化以外の用途で使っている場合があり、むしろ問題はそこ にある場合が多い気がする（論理削除時に他の項目も更新してたり）。 28

楽観ロック ⚫DBの悲観ロック（For Update）は、トランザクション内でのみ有効 ⚫ HTTPベースのシステムはステートレスなため相性が悪い ⚫楽観ロックの仕組み ⚫ 楽観ロックは、ロックというより上書き更新のブロック ⚫ 行のバージョン番号を用意して、更新ごとにカウントアップする ⚫ 番号の代わりにタイムスタンプを使いたくなるが、最近のハードウェ アは高速なので重複する可能性が無視できないためNG ⚫ 参照時にバージョン番号を取得し、更新時の条件句に指定する ⚫ 更新件数がゼロ件でなければ、参照してから更新するまでの間、その 行に変更は発生していないことが保証できる 29 取引先コード バージョン番号 0001 1 1 バージョン番号 2 バージョン不一致で 楽観ロックエラー 他の誰かが更新 参照時に バージョン番号 を控えておく 更新処理

参考文献 ⚫羽生章弘著「楽々ERDレッスン」 ⚫主張の異なる点もあるが、本資料はこの本の内容に大き く影響を受けている ⚫C.J.Date著「データベース実践講義」 ⚫梅田弘之著「グラス片手にデータベース設計シリーズ」 ⚫渡辺幸三著「業務別データベース設計のためのデータモデリ ング入門」 ⚫tgkさん「極北データモデリング」 30