ケーススタディ：データモデリングと設計

実務シナリオ + 設計判断の根拠 — Specialist試験対策

Case 1: EC企業の注文データモデル設計

背景

日本の中規模ECプラットフォーム（月間注文数100万件）。初期モデルが粗く、以下の問題が発生：

キャンペーン価格・通常価格・会員価格が混在し、「注文時点の価格」が追跡できない
商品属性（サイズ・カラー）がバリエーションによって異なるが、同一テーブルで管理している（EAVアンチパターン）
返品・交換の処理がアドホックに追加され、元の設計と整合しない

問題のある初期設計

-- アンチパターン：EAV（Entity-Attribute-Value）
CREATE TABLE product_attributes (
    product_id    INT,
    attr_name     VARCHAR(100),  -- 'size', 'color', 'weight' etc.
    attr_value    VARCHAR(500)
);

EAVの問題点（試験で問われる）:

データ型の制御不可（数値も文字列として格納）
特定属性での検索が極端に遅い
外部キー制約・NOT NULL制約が使えない
正規化の観点では1NFすら満たさない場合がある

改善された論理モデル設計

[OrderHeader]
- order_id (PK, Surrogate)
- customer_id (FK)
- order_datetime
- order_status_cd (FK → OrderStatus)
- channel_cd (FK → SalesChannel)

[OrderLine]
- order_line_id (PK, Surrogate)
- order_id (FK)
- product_variant_id (FK)
- quantity
- unit_price_at_order    ← 注文時点の価格を記録（重要）
- discount_amount
- list_price_at_order

[ProductVariant]
- variant_id (PK, Surrogate)
- product_id (FK)
- size_cd
- color_cd
- sku

[Product]
- product_id (PK, Surrogate)
- product_name
- category_id (FK)
- base_price

設計判断の解説

なぜサロゲートキーか？

ナチュラルキー（SKU等）はビジネスルール変更で値が変わる可能性がある
外部システムとの結合でキー体系が衝突しない
DWH（ディメンションモデル）との整合性が高い

価格を注文時点で記録する理由:

製品テーブルの価格は現在価格 → 過去の注文分析に使えない
SCD Type 2 で製品価格を管理するより、注文行に記録する方がシンプル
Audit要件（当時の取引証拠）にも対応できる

物理モデルへの変換（パフォーマンス考慮）

-- 論理モデルの 3NF を物理で部分的に非正規化
-- 注文検索のホットパス最適化
CREATE TABLE order_line (
    order_line_id        BIGINT PRIMARY KEY,
    order_id             BIGINT NOT NULL,
    product_variant_id   INT NOT NULL,
    quantity             INT NOT NULL,
    unit_price_at_order  DECIMAL(10,2) NOT NULL,
    -- 非正規化（JOINコスト削減）
    product_name_snapshot VARCHAR(200),  -- 注文時の商品名
    variant_desc_snapshot VARCHAR(100),  -- 注文時のバリアント説明
    INDEX idx_order_id (order_id),
    INDEX idx_created (created_at)
) PARTITION BY RANGE (YEAR(created_at));  -- 年次パーティション

設計原則: 論理モデルは3NF（整合性優先）、物理モデルは意図的な非正規化（パフォーマンス優先）

Case 2: SCD Type 2 の実装とピットフォール

背景

データウェアハウスの顧客ディメンション設計。顧客の住所が変わった場合に「以前の住所を使った注文」と「新しい住所を使った注文」を正確に分析したい。

SCD Type 2 の完全な設計

CREATE TABLE dim_customer (
    customer_key        INT PRIMARY KEY,        -- Surrogate Key（DWH用）
    customer_natural_id VARCHAR(20) NOT NULL,   -- ナチュラルキー（元システムのID）
    customer_name       VARCHAR(100),
    address_line1       VARCHAR(200),
    prefecture_cd       CHAR(2),
    
    -- SCD Type 2 制御カラム
    effective_from_dt   DATE NOT NULL,          -- この行が有効になった日
    effective_to_dt     DATE,                   -- この行が無効になった日（NULLは現在有効）
    is_current_flag     CHAR(1) DEFAULT 'Y',    -- 現在有効行フラグ
    dw_created_dt       DATETIME,               -- DWHへの挿入日時
    source_system_cd    VARCHAR(10)             -- ソースシステム識別子
);

実装のポイントと落とし穴

正しいクエリパターン:

-- 現在の顧客情報
SELECT * FROM dim_customer WHERE is_current_flag = 'Y';

-- 特定日時点の顧客情報（Temporal Query）
SELECT * FROM dim_customer
WHERE customer_natural_id = 'C001'
  AND effective_from_dt <= '2023-06-15'
  AND (effective_to_dt > '2023-06-15' OR effective_to_dt IS NULL);

よくある実装ミス:

effective_to_dt に NULL と '9999-12-31' が混在 → クエリが複雑化
複数レコードで is_current_flag = 'Y' が付く（ETL処理の冪等性が保たれていない）
Natural Key のインデックスがない → 特定顧客の全履歴検索が遅い

SCD タイプの選択基準（試験頻出）

SCD Type	使いどき	実務例
Type 1	誤り訂正・過去は気にしない	電話番号の誤入力修正
Type 2	完全な変更履歴が必要	顧客住所変更（地域別分析に影響）
Type 3	変更前/変更後の比較のみ	部門変更（現在と1つ前だけ）
Type 4	ヒストリーを別テーブルに分離	急速に変わる属性（価格等）
Type 6	Type 1 + 2 + 3 の組み合わせ	複雑なケース（Kimball推奨）

試験注意: Type 4, 5, 6 はFundamentals試験には出ないが、DW&BI Specialist試験では出題される。

Case 3: データモデルのアンチパターン診断

アンチパターン1: God Table（神テーブル）

症状:

-- 500カラム以上、あらゆるデータが1テーブルに
CREATE TABLE customer_all (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(500),
    credit_score INT,
    -- ... 497カラム続く
    order_count INT,      -- 集計値が入ってる！
    last_order_date DATE  -- 集計値が入ってる！
);

問題点:

NULLが多発（顧客タイプによって使わないカラムが多い）
集計値をトランザクションテーブルに持つと整合性が崩れる（参照整合性が貼れない）
スパースデータによるストレージ浪費

正しい対処:

正規化してサブタイプに分離（Individual / Corporate のような継承モデル）
集計値は Views または集計テーブルに切り出す

アンチパターン2: 意味のある複合主キー

-- 問題：2つのビジネスキーの組み合わせをPKにする
CREATE TABLE order_item (
    order_id     INT,
    product_code VARCHAR(20),
    -- ... 
    PRIMARY KEY (order_id, product_code)
);

問題点:

外部から参照しにくい（2カラム必要）
product_code 体系が変わるとPK変更が必要
変更時のカスケードアップデートが発生

正しい対処: サロゲートキーを追加してPKとし、業務キーはUNIQUE制約で保証

アンチパターン3: 汎用ステータステーブル

-- アンチパターン
CREATE TABLE statuses (
    entity_type VARCHAR(50),  -- 'order', 'customer', 'product'
    status_code VARCHAR(20),
    status_name VARCHAR(100)
);

問題点:

entity_typeで絞り込まないと不正なステータスを参照できる
外部キー制約が貼れない

正しい対処: エンティティごとに独立したステータステーブル（order_status, customer_status）

Case 4: データモデルレビューの実施

レビューの4つの観点（Specialist試験で出題）

1. ビジネス要件との整合性（Business Alignment）

すべてのビジネス概念がモデルに存在するか？
ビジネスルールが制約として表現されているか？
ビジネスユーザーが概念を認識できるか？

2. データ整合性（Data Integrity）

参照整合性が保証されているか？
NOT NULL / CHECK 制約が適切か？
CASCADE の動作は意図通りか？

3. 設計の一貫性（Design Consistency）

命名規則が統一されているか？（テーブル: PascalCase、カラム: snake_case等）
データ型が一貫しているか？（日付は常にDATE型か？）
パターンが一貫しているか？（全テーブルにサロゲートキーがあるか？）

4. パフォーマンス考慮（Performance Considerations）

高頻度クエリのインデックスが適切か？
N+1問題が発生しうる設計か？
パーティショニングが必要なほどデータ量が多いか？

レビューチェックリスト（実務で使える）

□ エンティティ定義がBusiness Glossaryと一致している
□ すべての必須属性に NOT NULL が設定されている
□ 外部キーにはインデックスが存在する
□ 命名規則ドキュメントに準拠している
□ SCD タイプの選択理由が文書化されている
□ ビジネスステークホルダーのレビューと承認が完了している
□ バージョン管理システムにモデルが保存されている

Specialist試験の頻出シナリオ問題

問題1

小売業のDWHチームが顧客セグメント変更（Gold→Silver等）を追跡する必要がある。変更前後の双方のセグメントで売上を集計したい。どのSCDタイプが最適か？

解答: SCD Type 2（新行追加で履歴を保持）。Type 3では直前の変更しか追えない。Type 1では履歴が消える。

問題2

データモデラーが「製品バリアント（サイズ×カラー）」の属性設計で悩んでいる。バリアントごとに属性数が異なる（靴にはサイズがあるが色はない場合も）。EAVパターンを提案されたが、データモデラーとして何を推奨するか？

解答: EAVは避ける。代わりに、

ProductType別にサブタイプテーブルを設計（型安全性の確保）
もしくはJSONカラムを使ったhybridアプローチ（NoSQLカラムをRDBMSに埋め込む） EAVは検索・制約・型安全性の全てで劣る。

問題3

Reverse Engineeringの主な目的は？

解答: 既存のDBから論理・概念データモデルを再構築し、文書化すること。レガシーシステムの理解、移行計画の策定、コンプライアンス要件への対応に使用される。