データモデリングチェックリスト48

フェーズによって違うモデリング

論理データモデリング

物理データモデリング

論理データモデリング

目的1: 信頼性の高いデータを格納する

目的2: 将来に渡り一元的にデータを管理する

物理データモデリング

ER モデル

依存は FK が PK の一部となる。つまり参照するデータがなければレコードを作成できない

受注業務において受注データと受注商品データは同タイミングで発生し取り消しも同じタイミング

独立は FK を非キーとして持つ。参照先のエンティティに依存せず独立して存在できる

顧客データや商品データは、受注データとは別に発生する

受注取引がなくても顧客データを持てる：顧客|─◯受注

受注取引がないと顧客データを持てない：顧客|─|受注

データモデリング方針策定

つまり関係者複数が同じゴールを目指すため

目的：共通認識を持つ。コミュニケーションミス・手戻り排除。

実施手順：対象となる作業範囲・体制の確認、情報共有のルール、データ標準化ルールの決定

紙面に方針概要表組みあり

システム化の明確な目的、どの業務で誰がどのようなデータを利用するか

これも紙面の役割分担を見たほうが良い

データ項目がどういった意味があり業務でどう利用されているか分からないというのが大きな壁になる

同じ項目に見えるものもリレーションシップを作っていくと実は中身が違っていた、もしくは項目は違うが実は同じ項目だったということもある（取引先、得意先、顧客）

なおデータベースへの要求確定の権限を持てる業務部門の意思決定者も決めておくことも重要

関係者との情報共有確かに難しそう。共同成果物、進捗確認という観点で何を出力するかは大事そう

ドキュメントの類も紙面にあるので参照

なお上記を作っても実際の物理テーブルで乖離しては困るので整合性を保ち、sync すべき

データ属性の標準化：データ型、とくに日付や時間、signed/unsigned、精度、何を決めるべきかを決める

データ名称の標準化：「担当者」だけでは意味が多重になる場合、社内担当者・顧客担当者など使い分けをはっきりする

キーは主要語、修飾語、区分語（データ値の区分かな）

table:データ名称付与規則

エンティティ名称 = 修飾語（※ 1つ以上） + 主要語

例）商品別売上 = 商品別 + 売上

アトリビュート名称 = 修飾語（※ 1つ以上） + 主要語 + 区分語

例）期末在庫数量 = 期末 + 在庫 + 数量

〇〇数量、〇〇名、〇〇金は区分を指すことが多い

論理データモデリング①

ゴールはビジネスルールが反映された、重複や矛盾の削除（= 正規化・最適化）

code:plain.txt

データモデリング方針 ⇢ 正規化 ⇢ 最適化 ⇢ ビジネスルール検証 ⇢ 安定性検証 ⇢ 論理データモデル・成果物レビュー

ここでは最適化まで

まずは、方針確認

データ洗い出しが完了していないことには始まらない（なければ未確定部分がどこか・誰に確認すべきか押さえる）

正規化

データの重複削除、そうしても矛盾をしない、不整合のおきないデータ構造にすること

第一正規形、第二正規形、n正規形がある。ここでは第三正規化まで

対象範囲が広くなると作業が大変なので一画面、一帳票ずつ

小さな正規化モデルが複数出来上がるが最適化で整理・統合するのでここではやらない

例えば商品の受注

https://gyazo.com/728e5d920043fcec51251c99a23b5d2b

分離する際には FK として分離元のレコードと紐付け

どの受注の商品かを識別するためには受注番号 + 商品番号を複合キーとする

ここまでで重複が解消できたので第一正規化が完了 ⇢ 受注、受注商品エンティティ

ただ、商品名の変更が複数レコードに及ぶ、商品情報が新規受注によってのみしか作られない、などの不整合の問題

受注商品エンティティは商品番号・商品名・商品単価の項目が繰り返し発生している ⇢ 問題

第二正規化では関数従属性の排除を行っていく

受注商品から商品番号を PK にした商品エンティティへ分離

https://gyazo.com/58a23575f597ca5dbf63df6c666eeff5

次の課題が出てくる ⇢ 注文とともに顧客データが作られる、受注データを削除すると顧客データが失われる

第三正規化

https://gyazo.com/fbba7a185245c6e30adecd07346d0069

受注には顧客番号、顧客名、顧客住所の 3 つの非キーがある ⇢ 顧客番号によって他が決定的である従属関係

顧客番号を PK にして顧客エンティティに分離できる。受注エンティティとの関連は顧客番号を FK にして定義

⇢ 正規化では導出項目は削除する

ここまでで帳票のデータ項目が抜け漏れなく正規化モデル上のエンティティにマッピングされたか確認が必要

注意としては正規化モデル上のアトリビュート数は元のデータ項目と同じではない（PK, FK などがあるため）

正規化はデータ項目ひとつひとつビジネスにおける意味合いを確認・整理・仕分けしていく作業。確認は必須

とくに非キーと非キーの関数従属性を検証するので重要な項目や意味合い確認の漏れが出やすい

商品単価、消費税などは変動するので導出項目として削除すると当時の受注合計などが導出できなくなる

ほかにも小数点以下四捨五入するというルールで計算したかという記録を残す場合にも必要となる

経営資源であるヒト・モノ・カネを管理する Resource 系

顧客、商品、従業員、勘定科目…

時間的要素・数量的要素を含む事業・業務活動を表す Event 系

受注、製造、出荷、請求、予算…

区別をするのはデータが整理された第三正規化の最後にやるとよい（ER 図では R, E などと書き分けたりする）

最適化

小さな単位の、複数の正規化モデルを分析し同じエンティティは統合しリレーションを再度見直す

ポイント

一度に複数統合しようとしない ⇠ 見落としや削除を避けるため

比較検証・統合の作業は4段階の繰り返し

同じ PK を持つエンティティに着目

それぞれのエンティティのアトリビュートに注目し名称・データ属性を確認して統合可否を判断

統合可能であれば名称・データ属性を調整してアトリビュートの重複を避けていく

統合したあとに関連するリレーションに矛盾がないか確認する

PK やアトリビュート構成が似ているからといって単純に統合してはいけない

業務分析と実務に詳しい担当者に確認しないと手戻りが大きくなる

名称だけで判断せず管理対象を見極める

PK が同じでも管理対象の意味が異なる、アトリビュート構成が異なる、こういった場合は安直な統合は NG（PK の名称見直しを）

https://gyazo.com/41beae878938ca04678994c099f40bee

上記は一見違うPKに見えるが実際は同じPK、統合対象を見失わないようにする

最適化を終えても第三正規形を維持しているはず、だが違反状態を生んでいれば確認

例えば受注エンティティの "受注担当者" アトリビュートが社員エンティティの "社員名" アトリビュートのシノニムであることを見落とすと重複管理となりデータの一意性がなくなってくる

論理データモデリング②

前段では正規化モデルと統合をおこなった

しかしビジネスルールに則っていなければシステム化できない

ここではビジネスルールに則しているか検証する、以下チェック項目は代表的な検証例

納品エンティティ:請求エンティティのカーディナリティが 1:1 な場合

意味：「納品の都度請求する」ということを表す

ビジネスルールでは「1回の注文を複数回に分けて請求したい」ではカーディナリティに矛盾出てくる ⇢ 1:多 なはず

1つのエンティティ内で異なる意味合いのデータを管理しているケースが出てくる

判断材料としてはエンティティ内に「区分」「分類」「種別」といった名称のアトリビュートがあるかどうか

名称によって意味を分けようとしている証左であるため

https://gyazo.com/2ae4d858856f9d2bc6884b0667c5c2fc

分ける前ではアプリケーション側で重量 NULL などを判断する必要がある

ここでは PK の「主要語」に着目、同じ主要語の候補があれば共通点を検証する

法人顧客、個人顧客の PK は主要語が顧客、同じく顧客という共通概念

法人・個人の区別なく、顧客として扱いたいのなら顧客をスーパータイプとして定義できる

アトリビュート構成が違うこともあり、このケースは相違のサブタイプと呼ぶ

https://gyazo.com/9bc1efa7d83e26bbd64802e94a61a9a9

安全性検証

ビジネスルールの検証によりビジネスを反映した形になってきた

データモデルを長く使い続けるためには変化を許容できる安定した構造にしておく必要がある

組織・体制、商品・サービスの分類、取引条件・個別業務など…ビジネスルールは変化する

https://gyazo.com/ccad07a83d4476d3ad587dddb043fca1

請求先と納品先は同一でよいというルールがあったとしても、将来的に請求先は本社で納品先は工場といったようなこともありえる

請求エンティティも顧客番号を FK に持つことで納品先=請求先というリレーションシップを変えることができる

ビジネスルールの変化を考える際はカーディナリティが「1」の部分に着目し検証すると良い

一括納品しかできなかったデータモデルを分割納品できるようにする例

https://gyazo.com/e9edc08c7623be5b15ca515f46c8b99a

リレーションシップの安定性検証と同じ進め方、PK がビジネス変化に対応可能か検証

特に中が必要なプライマリーキー

社外コード：JAN コード、行政などの発行するコード、コード体系を制御できないもの

有意コード：先頭2桁、後続3桁とかデータ値に意味をもったコード

複数のアトリビュートから構成されるプライマリーキー

社外コードをPKにしている場合の安定性検証

例えば企業コード、これを PK とすると FK として他のエンティティが参照している場合に一気に破綻

PK に有意コードを使用している場合の安定性検証

コード体系に変更があるとこの PK を FK としたデータの追従が必要になり安定しない

そこで商品番号は商品 KEY のような代用キーとして FK として部門やコードに FK を定義する

https://gyazo.com/301cf783e41a2f808a497340dc2bc776

PK に複合キーを使用している場合の安定性検証

https://gyazo.com/95b0261fc61102886948eb4f2b1e23cf

受注番号・商品番号で複合キーを採用した際に一度注文確定したあとは受注数量の増減希望があってもデータ変更ができなくなってしまう + 数量変更の履歴がどこにもなくなる

PK として受注明細番号を代用キーとして採用し複数回商品番号が登録できるようにする

見落とし作業ミスが高リスクとなるので PK の安定性検証とリレーションシップの安定性検証は同時に行わない

論理データモデル成果物レビュー

ER 図によりエンティティとリレーションシップで表現されたとしても慣れてない人には理解しづらい

工夫すると良さそうな点

エンティティタイプの違いを可視化する（リソース、イベント）

時系列（業務フロー）を表現する

販売業務、アフターサービスなど特定業務の範囲をサブエリアとして可視化する

https://gyazo.com/31630eb3bddcd42c899647d7b56c473d

特に変更点やこれまでのデータを踏襲しなかった理由などの説明は承認において重要そう

物理データモデリング①

内部構造の決定と、現実的なデータベースを実装。論理データモデリングではビジネス ↔ データの一貫性、物理データモデリングではシステム ↔ データの一貫性・整合性に着目

https://gyazo.com/2d16d5e2362c439a04a93277deec30f2

C がない場合は必要なプロセスが欠如していないか確認。重複していればプロセスが多重化している

これ本当か？？？

リレーションシップの親子関係がある場合はデータの作成と同時に子のエンティティにもデータが生成される

イベント、リソースエンティティで分ける。リソースエンティティには時間軸はないのでグルーピングして後ろに配置

プロセスを上から並べイベントエンティティを左から発生順に並べる

https://gyazo.com/16eaa267937772195f0a6f42ac65167b

頻繁にデータ更新・削除が行われるとデータベース上で断片化が発生しやすく応答性能が劣化する可能性

U,Dの多いエンティティでは待機が多く発生する可能性がある

更新削除の順番に規則性をもたせてデッドロックが発生しないようにする

CRUD 図で各プロセスの利用頻度、ピーク時の予想実行数などを想定するとボトルネックになりえる箇所が見つかる

CRUD が多いプロセス・エンティティ

実行頻度が高いプロセス

データ件数が多いエンティティ

重要なユーザが利用するなど、重要度の高いプロセス

1. プログラムの見直し（SQL 含む）

処理ステップの分割やスカラ型から配列型への変更、ロジック見直しやチューニング、テーブル構造変更なしという前提で処理の改善を図ることができないか

2. RDBMS 固有機能の検討

データベースバッファキャッシュ、物理ディスクアクセス抑制、更新処理に対するロギング停止など

3. インデックス作成

検索処理の性能改善。ほかのプロセスの更新処理が遅くなる影響も考える

4. データ構造の変更

これは極力行わない。データの整合性や一貫性という品質面・保守面の対価を多く払うことになる

実装する RDBMS に定義可能な長さにするために、論理データ名称を英語またはローマ字の母音を除いた短縮形にするのが一般的です

そうなの？？？

日付は日付型、文字列型、数字型を選べる。プロセスで日付関数が用いられていたり時間計算が必要なら日付型に

例えば製造中止の製品がデータベースでは存在していることになっていて、製品列挙で候補として上がってくるのは矛盾

矛盾排除のためには INSERT、UPDATE、DELETE のタイミングで、テーブルのカラムごとに格納できるデータ値のルール ＝ 制約を決める。これが制約設計

カラムのデータ仕様（入力するデータ値に関する定義）確認

データ制約の適用検討

データ制約の定義

https://gyazo.com/07b55ecbb3f8535453aa4ee08014e51f

ある意味、データ入力時の RDBMS が可能な制御機構、アプリケーション側のバリデーションに近い

ドメイン制約

データが正しい範囲にあることを保証する制約（enum で 0: 未対応、1: 対応中... など）

一意性制約

ユニーク制約とも言う。これは PK 制約、複合ユニークキー制約などもある

NOT NULL 制約

関連制約

code:sql

CREATE TABLE "受注" (

"受注番号" NUMBER(10) PRIMARY KEY,

"受注日" DATE NOT NULL,

"出荷予定日" DATE,

CHECK("受注日" < "出荷予定日") -- これが関連制約

);

導出制約

code:sql

CREATE VIEW "受注ビュー" (

"商品単価", "受注数量", "受注明細金額"

) AS

SELECT P."商品単価", D."受注数量",

P."商品単価" * D."受注数量" -- これが導出制約

FROM 受注 H, 受注明細 D, 商品 P

参照制約

https://gyazo.com/0a892f41afdcb1bd77361b0b94fad46c

参照制約のデータ操作時にどういったアクションを適用するか

親テーブルに対する変更制御として CASCADE か RESTRICT かは親子のリレーションシップを確認する

依存していれば CASCADE 非依存の場合は RESTRICT を指定するのが一般的

カラム一覧に制約内容を入れる欄を設けるとモレのない定義ができる

データ制約はデータベース側でやるかアプリケーション側でやるかという2通りある

ユニーク性や範囲などのドメイン制約はデータベースでもアプリケーションでも実装可能

データベース側で制約をもたせる利点は宣言が容易で制約をもつコーディング自体が不要になる

アプリケーション側で制約をもたせる利点は、柔軟性をもたせることができる、データベースへの問い合わせなしに検証できるものもある

一方でアプリケーション内で閉じるとブラックボックス化してルールが見えてこないこともある

物理データモデリング②

性能と安定性担保のためにデータベースを調整

インデックス設計

頻繁に利用しそうなカラムにインデックスを定義しておくと性能考慮がしやすい

データアクセス

フルスキャン＝全表走査と、インデックススキャン＝索引走査の2つ

WHERE 条件節で特定データで絞るケースではフルスキャン＜インデックススキャン

インデックスを定義してもインデックス用物理領域のメンテナンスで使う・使わないは RDBMS が判断する、なので

インデックスは使わなければ性能があがらない

インデックス用に確保した領域が使わなければディスクが無駄

追加・更新・削除のたびにインデックスのメンテナンス負荷が高まり、さらにディスクのムダや断片化が生じる

https://gyazo.com/3c6e454403c82a5b7959d558feda0d54

インデックス設計の手順

1. テーブルの選択：インデックス対象カラムの前に、数十万件以上のテーブルを対象候補とする

2. インデックスを設定するカラム選択：WHERE 条件節で指定するカラム（行選択率が15%以下となる場合）、テーブル結合における条件キー、ORDER BY 句のソートキーとなるカラムが候補となる

3. 影響度の検証：インデックスを定義したカラムにテストで追加・更新・削除をテスト的におこなう。CRUD 図を見ながらプロセスへの影響度を検証する

以下のような記述が SQL 文に含まれているとインデックスを効かせても利用されない

https://gyazo.com/ceaebaabfb2b06b70c7edb8d77178609

インデックスを作成すると追加・更新・削除の処理速度が低下する

CRUD 図にインデックス定義をするカラムを追記、C, U, D のついたプロセスに着目

e.g. 顧客エンティティで顧客名にインデックスを張っている場合、顧客登録・顧客情報変更・顧客削除あたりはプロセス処理性能に影響がある可能性

（性能テストへの言及はそこまでないな…）

インデックス定義は「性能低下とメンテナンス工数とうデメリット VS 性能面のメリット」

性能テストの結果を鑑みてインデックス作成するかの可否判断

（普段からそこまで意識してやらない気がする）

https://gyazo.com/5ce551d36cc4fde55951f7f330d9ae4b

データ容量見積もり概算

初期データ容量 = 初期データ件数 x 平均行サイズ

増分データ容量 = データ増加件数 x 平均行サイズ

最大データ容量 = 初期データ容量 + (増分データ容量 x 保存期間)

インデックスサイズ概算

初期データ容量 = 初期データ件数 x (インデックスカラムの合計サイズ + 行 ID のサイズ)

最大データ容量 = 最大データ件数 x (インデックスカラムの合計サイズ + 行 ID のサイズ)

おそらく普段はここまで手入れすることはなさそう。ざっくり見積もってクラウドで任意のグレードのインスタンスを選ぶ、みたいな

トランザクション・ロールバック用の UNDO 領域とか普段あまり考えたりすることはない気がする（DBA なら考える？

性能面では物理領域へのI/Oが競合しないよう検討

トランザクション処理でデータ更新が頻繁 ⇢ UNDO 領域・ログファイルのため SSD を利用した I/O の速い領域を割当てるなど

一時領域で I/O が生じるのはソートや結合・合計処理を行うときだし、インデックスの場合はテーブルデータがオリジナルなので復元はいつでも可能

⇢ 一時領域やインデックス用の物理領域は冗長化されてない・信頼性の高くない領域割当も検討できる

まとめ

データ整理とデータ調節の 2 つの視点でデータ構造を設計することが重要