Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@morinki

DB設計⑥

Posted at

論理設計のバッドノウハウ

DBをマスターしたい

前回に引き続き、DBの基本知識を学習している
今回は論理設計のバッドノウハウについてまとめる
真似してはいけない設計のパターンをIT業界では、
「アンチパターン」や「バッドノウハウ」と呼ぶ

参考文献

達人に学ぶDB設計徹底指南書

目次

  1. 非スカラ値（第１正規形未満）
  2. ダブルミーニング
  3. 単一参照テーブル
  4. テーブル分割
  5. 不適切なキー
  6. ダブルマスタ
  7. バッドノウハウのどこが悪い？

はじめに

世の中にはダメな設計が溢れかえっている
そんな設計が生まれる理由は「何も考えていない」ことによるものである
これを学び、ポイントを押さえた論理設計ができるようにしたい

1.非スカラ値（第１正規形未満）

社員ID 社員名 子
000A 加藤 達夫
信二
000B 藤本
001F 三島 敦
飛鳥
洋子

これは正規化の際に複数のデータを入れないとしました。
しかし、RDBの標準機能に「配列型」というものがあります
理由はJavaなどのプログラミング言語では配列型を扱うため、
リレーショナルデータベースに配列型が実装されないと
データの受け渡しで齟齬が生まれるためです。

  • 「配列型」は機能としてサポートされているが現在も普及には至っていません
  • 結論、第一正規形を優先する
  • 情報は可能な限り分割する→ただし、意味が壊れるほどの分割はしない

2.ダブルミーニング

１つの列に複数の意味が含まれている列が発生する状態

年度 学生名 列１ 列２
2001 石田純 170 62
2001 綾瀬はるみ 155 58
2001 北島六郎 165 60
2001 山下智樹 175 64
2002 小泉淳二 170 18
2002 相川亮 180 17
2002 山田孝 185 18
2002 香川慎吾 172 19

5行目から「列２」の中身が変わっている
上記の例で言うと、体重から年齢？に変わっている

テーブルの列は変数ではないので一度決めたら変更不可

3.単一参照テーブル

単一参照テーブルの具体例

　似た組み合わせのテーブルを１つのテーブルにまとめたもの
　「ID」＋『名称」のようなテーブルを１つにしてしまうイメージ

コードタイプ コード値 コード内容
comp_cd C0001 A商事
comp_cd C0002 B化学
comp_cd C0003 C建設
・・・
comp_cd C9999 Z工業
pref_cd 01 北海道
pref_cd 02 青森
・・・
pref_cd 47 沖縄
sex_cd 0 不明
sex_cd 1 男
sex_cd 2 女

上記は、それぞれのマスタテーブルを１つにまとめたようなもの
「会社コード」、「県コード」、「性別コード」が１つのテーブルになっている

単一参照テーブルの功罪

　ダブルミーニングを一般化したような手法
　自然と多くの開発現場で使われてしまっている
　オブジェクト指向言語における「ポリモルフィズム」に似ている
　※ポリモルフィズム：多様性、オブジェクトが複数の型に属するという概念

＜利点＞
　・マスタテーブルの数が減るため、ER図やスキーマがシンプルになる
　・コード検索のSQLを共通化できる

＜欠点＞
　・「コードタイプ」「コード値」「コード内容」の各列とも、必要な列長が異なる
　　→大きめの可変長文字列型で宣言が必要＝データ量が無駄に増える
　・１つのテーブルにレコードが集約する
　　→場合によっては、レコードが多くなり検索のパフォーマンスが悪化する
　・コード検索のSQL内でコードタイプやコード値を間違えてもエラーにならない
　　→バグに気づきにくい
　・ER図がスッキリするが、ERモデルとしては正確性を欠く
　　→ER図の可読性を下げる

4.テーブル分割

テーブル分割の種類

  • 水平分割
    　レコード単位にテーブルを分割する方法
  • 垂直分割
    　列単位にテーブルを分割する方法
年度 会社コード 売上(億円)
2001 C01 50
2001 C02 52
2001 C03 45
2001 C04 55
2002 C01 60
2002 C02 40
2002 C03 48
2002 C04 54
2003 C01 47
2003 C02 41
2003 C03 44
2003 C04 58

上記のサンプルも実際には何百万〜何十億のレコードになることもある
その結果、テーブルにアクセルするSQL文のパフォーマンスが悪化することもある
　→最大の原因はストレージに対するI/Oコストの増大
　アクセスするデータ量を減らすことがパフォーマンス改善の手段となる

水平分割

　先ほどのサンプルを年度ごとに水平分割することでテーブルサイズを小さくする

年度 会社コード 売上(億円)
2001 C01 50
2001 C02 52
2001 C03 45
2001 C04 55
年度 会社コード 売上(億円)
2002 C01 60
2002 C02 40
2002 C03 48
2002 C04 54
年度 会社コード 売上(億円)
2003 C01 47
2003 C02 41
2003 C03 44
2003 C04 58

わかりやすい解決策だが、重大な欠点があるためRDBでは原則禁止されている
＜欠点＞

  • 分割する意味的な理由がない
    →パフォーマンスによる物理レベルの要請によるものなので、要請がなければ実施は不要
  • 拡張性に乏しい
    →この場合、全年度のデータを総なめで検索しない前提で成立する
    　また、テーブルが次々に増えてゆく
    データの形がプログラムを決めるというDOAの原則に反する
  • 他の代替手段がある
    →DBMSに「パーティション」という機能が存在する
    　パーティションキーを軸として格納領域を分離することができる

＋α)パーティションとインデックスの使い分け
　一般的には下記のような場合の列にパーティションを利用する
　　・カーディナリティが少ない（十〜数十)
　　・値の変更が起きない

垂直分割

会社コード 社員ID 社員名 年齢 部署コード
C0001 000A 武田 40 D01
C0001 000B 藤本 31 D02
C0001 001F 山下 25 D03
C0002 000A 斎藤 48 D03
C0002 009F 渋谷 50 D01
C0002 010A 太田 21 D04

上記のテーブルに対する検索のSQL文に遅延が発生して改善が必要だとする
かつ、検索の利用例が常に「会社コード」「社員ID」および「年齢」だとする
これらの場合は下記のように分割することでSQLのアクセスするデータ量を減らせる

会社コード 社員ID 年齢
C0001 000A 40
C0001 000B 31
C0001 001F 25
C0002 000A 48
C0002 009F 50
C0002 010A 21
会社コード 社員ID 社員名 部署コード
C0001 000A 武田 D01
C0001 000B 藤本 D02
C0001 001F 山下 D03
C0002 000A 斎藤 D03
C0002 009F 渋谷 D01
C0002 010A 太田 D04

しかし、これも分割する論理的な意味がないので原則利用するべきではない
また、垂直分割は「集約」で代替可能である

集約

　集約はテーブル分割の代替案として位置付けられる方法である
種類は２種類挙げられる
どちらもオリジナルデータは残した対策となる
オリジナルデータ↓

会社コード 社員ID 社員名 年齢 部署コード
C0001 000A 武田 40 D01
C0001 000B 藤本 31 D02
C0001 001F 山下 25 D03
C0002 000A 斎藤 48 D03
C0002 009F 渋谷 50 D01
C0002 010A 太田 21 D04
  • 列の絞り込み
    ・元のテーブルから必要な列だけを持った新規テーブルを追加作成する
    ・上記のような小規模なテーブルをデータマートと呼ぶ（省略してマート）
    ・注意点はデータ同期が必要（適切な更新頻度が必要となる）

データマート↓

会社コード 社員ID 年齢
C0001 000A 40
C0001 000B 31
C0001 001F 25
C0002 000A 48
C0002 009F 50
C0002 010A 21
  • サマリテーブル
    ・集約関数によってレコードを集約した状態で保持したテーブル
    ・データマートと同じく、更新のタイムラグによるデータの不整合が問題となる
集約関数
    SELECT 会社コード, AVG(年齢) AS 平均年齢
        FROM 社員
    GROUP BY 会社コード

サマリテーブル↓

会社コード 平均年齢
C0001 41
C0002 35

5.不適切なキー

可変長文字列はキーに使用しない

キーとは下記の２つ
・主キー、外部キーなどデータベースの機能で設定されるもの
・テーブルの結合条件で使用される列（結合キー）

使用してはいけない理由
・不変性がないこと
・固定長文字列と混同すること

キーは永遠に不変

　可変長文字列に使用するデータは名前などに採用されるのは自然
　将来的に変化する可能性もある＝コロコロ変わるのはキーに不向き

同じデータを意味するキーは同じデータ型にすべし

　固定長文字列と可変長文字列は同じ文字列を保持しても、物理的には同じにならない
・固定長文字列：'テスト　　' 　←スペースが入っている
・可変長文字列：'テスト'

6.ダブルマスタ

顧客マスタA　　　　　　　　顧客マスタB

顧客コード 顧客名
C001 山田　太郎
C002 中島　健二
C003 新谷　沙耶
C004 古谷　瑛二
顧客コード 顧客名
C001 山田　太郎
C002 中島　健二
C003 新谷　沙耶
K001 澤田　亜美

上記のようにマスタテーブルが複数あると、テーブルの結合が必要になる
→SQLで言うと完全外部結合or UNIONで実現できるがコストの高い処理
　＝パフォーマンスが悪くなる

結合の具体例

完全外部結合
    SELECT COALESCE(A.顧客コード, B.顧客コード),
           COALESCE(A.顧客名,　B.顧客名)
    FROM　顧客マスタA A FULL OUTER JOIN 顧客マスタB B
      ON  A.顧客コード =　B.顧客コード;

完全外部結合は両方のテーブルのレコードを保存する
COALESCE関数は引数から左をスキャンして、最初に見つけたNULLでない値を返す

UNION
    SELECT A.顧客コード,
           A.顧客名
    FROM   顧客マスタ
    UNION
    SELECT B.顧客コード,
           B.顧客名
    FROM   顧客マスタB B;

どちらも結果は以下になります

結果
    顧客コード　　顧客名
    --------    ---------
    C001        山田　太郎
    C002        中島　健二
    C003        新谷　沙耶
    C004        古谷　瑛二
    K001        澤田　亜美

ダブルマスタはシステムの統廃合によって生じる

7.バッドノウハウのどこが悪い？

①可読性

　バッドノウハウを採用した場合、開発および運用コストは何十倍にも高くなる
　その最大の理由は、設計に対する理解を妨げるから
　例えば、ダブルミーニングや単一参照テーブルは最初に設計した人しかわからない
　→人間にとって「わかりにくい」システムは理解やコミュニケーションを阻害する
　＝バグを生み出し開発効率を落とす温床となる

②設計変更の難しさ

　RDBを開発の後工程の段階になってから修正することは難しい
　テーブル構成にあとから手を加えると、アプリケーションにも修正が必ずいく
　原則として「ERモデルの手戻り」はかなり代償が高くつく
　もし、ERモデルを修正するとアプリも作り直してを繰り返す（ITでは「デスマーチ」）

③データ構造がコードを決めるのであって、その逆ではない

　データ構造がダメな状態で、プログラミングによって挽回することはできない

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?