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

概要

この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。

この記事では、論理設計における様々なバッドノウハウをケース別に解説しています。

非スカラ値(第一正規形未満)

第一正規形とはテーブルに含まれるすべての値がスカラ値(一つのセルには一つの値)であることを指す。

このスカラ値のルールを守っていないバッドノウハウについて以下で述べる。

配列型による非スカラ値

そもそも、データベースには、非スカラ値を含むテーブルを作る機能がある。

具体的には、以下のように、テーブルの一つの列を配列型として定義する機能がある。

配列型カラムを持つテーブルの作成

CREATE TABLE huyosha (
  shain_id varchar(4),
  shain_mei varchar(20),
  kodomo varchar(20) [], -- 配列型の宣言
  PRIMARY KEY(shain_id));

配列型カラムを持つテーブルへレコードを格納するSQL文

INSERT INTO huyosha VALUES ('000A', '加藤', '{達夫, 信二}');
INSERT INTO huyosha VALUES ('000B', '藤本', NULL);
INSERT INTO huyosha VALUES ('000F', '三島', '{敦, 陽子, 清美}');

しかし、配列型を採用するには、単にデータベース内部に閉じた設計だけでなく、接続するアプリケーションやミドルウェアとの整合性を考慮しなければならないので、設計コストが大きい。

基本的には、配列型を利用せず、第一正規形を守るべきである。

ダブルミーニング

ダブルミーニングとは具体的には、以下のテーブルのように一つの列の値が二つの意味を持つ場合である。

年度	学生名	列 1	列 2
2001	山田太郎	170	62
2001	鈴木花子	165	55
2001	佐藤次郎	175	70
2002	伊藤三郎	180	15
2002	高橋四郎	185	19
2002	中村五郎	170	22
2002	小林六郎	190	18

列 2 は 2001 年度までは体重であり、2002 年度からは年齢を表すようになった
ダブルミーニング列は運用途中で意味が変わってしまうため、「体重」「年齢」といった固定的な列名をつけることができない

テーブルの列は「変数」ではない

ダブルミーニングの列が生まれる理由は、テーブルの列をなんでも格納できる「変数」として扱っているため。

しかし、リレーショナルデータベースにおけるテーブルは実世界におけるいろいろな実体(エンティティ)の「写像」であるため、変数よりもずっと静的で固定的な存在である。

つまり、テーブルや列の意味は、一度決めたら変更不可というのが原則である。

上記のようなテーブルは以下のように新しい「年齢」列を追加するのがダブルミーニングを避けるプラクティスである。

2001 年の状態:

年度	学生名	身長	体重
2001	山田太郎	170	62
2001	鈴木花子	165	55
2001	佐藤次郎	175	70

2002 年の状態:

年度	学生名	身長	体重	年齢
2001	山田太郎	170	62
2001	鈴木花子	165	55
2001	佐藤次郎	175	70
2002	伊藤三郎	180		15
2002	高橋四郎	185		19
2002	中村五郎	170		22
2002	小林六郎	190		18

単一参照テーブル

単一参照テーブルとは、ある意味で前項で述べたダブルミーニングを一般化した手法である。

以下のような第三正規形を満たすテーブルを考える。

社員テーブル:

会社コード	社員 ID	社員名	年齢	部署コード
C0001	000A	山田太郎	20	D01
C0001	000B	鈴木花子	25	D02
C0002	001F	佐藤次郎	30	D03

会社テーブル:

会社コード	会社名
C0001	株式会社 ABC
C0002	株式会社 XYZ

部署テーブル:

部署コード	部署名
D01	営業部
D02	人事部
D03	総務部

ここで、会社テーブルと部署テーブルがともに「識別 ID」+「名称」という共通構造を持っていることがわかる。

つまり、あえて二つのテーブルに分けず、一つのテーブルにまとめることが可能である。

また、テーブル数が膨大になると、同じ構造のデータであれば一つのテーブルにまとめていた方が管理がしやすい。

このような考えから作成されるのが以下の単一参照テーブルである。

コードタイプ	コード値	コード内容
comp_cd	C0001	株式会社 ABC
comp_cd	C0002	株式会社 XYZ
略	略	略
sec_cd	D01	営業部
sec_cd	D02	人事部
sec_cd	D03	総務部

単一参照テーブルを使うべきか

ダブルミーニングでは列の意味がレコードによって変化したが、単一参照テーブルにおいてはテーブル全体が、ある時は「会社」であり、ある時は「部署」というように変化するのが特徴(オブジェクト指向におけるポリモルフィズムに近い)。

単一参照テーブルのメリット・デメリットは以下のようになる。

メリット:

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

デメリット:

「コードタイプ」「コード値」「コード内容」の各列とも、必要とされる列長はコード体系によって異なるため、余裕を見てかなり大きめの可変長文字列型で宣言する必要がある
一つのテーブルにレコードを集約するため、コード体系の種類と数の多さによっては、レコード数が多くなり、検索のパフォーマンスが悪化する
コード検索の SQL 内でコードタイプやコード値を間違えて指定してもエラーになることがないため、バグに気づきにくい
ER 図がスッキリするとはいっても、ER モデルとしては正確さを欠いており、かえって ER 図の可読性を下げることになる

このように、まとめるとデメリットの方が大きいので、基本的には単一参照テーブルは不要である。

テーブル分割

テーブル分割は、一般的に巨大テーブルのパフォーマンス向上を行うために実施され、ものによってはバッドノウハウでなく現実的な解決方法となる場合もある。

テーブル分割には、以下の種類が存在する。

水平分割
垂直分割

水平分割

水平分割は、レコード単位でテーブルを分割する方法。

会社の年度売上テーブル:

会社コード	年度	売上
1	2021	120000000
2	2021	80000000
1	2022	150000000
2	2022	95000000
1	2023	180000000
2	2023	110000000

例えば、上記のテーブルが拡張され巨大化した場合、以下のように年ごとにテーブルを水平分割することで、アクセスするテーブルのサイズが減少するので検索パフォーマンスの向上が期待できる。

会社コード	年度	売上
1	2021	120000000
2	2021	80000000

会社コード	年度	売上
1	2022	150000000
2	2022	95000000

会社コード	年度	売上
1	2023	180000000
2	2023	110000000

しかし、次項で述べるような重大なデメリットが存在するので、リレーショナルデータベースでは水平分割は原則禁止である。

水平分割のデメリット

分割する意味的な理由がない
- 分割する理由が、正規化の理論から発生したものではなく、純粋にパフォーマンスという物理レベルの要請によるものなので、それらがなければ実施する必要がない
拡張性に乏しい
- 例えば、例に挙げたテーブルの水平分割がパフォーマンス向上に効果を発揮するのは、「全年度のデータを総なめで検索することはない」という前提が成り立つ場合に限られる
- 「経年変化を分析したい」という要件が新たに発生した場合、例に挙げた水平分割では対応できない
- 例の場合は、年度ごとにテーブルが次々に増えていくことになるので、その度にアプリケーション側も改修が必要となる
他の代替手段がある
- 多くの DBMS が「パーティション」という機能を持ち、テーブルを分割することなく、パーティションキー(例の場合は年度)を軸として物理的に格納領域を分離できる(SQL のアクセス量は1/n(パーティションの数))

垂直分割

垂直分割は、レコード単位でテーブルを分割する水平分割に対して、列を軸に分割する方法。

会社テーブル:

会社 ID	会社名	設立年	業種	従業員数	都道府県	住所	電話番号	メールアドレス	年間売上	利益	評価
1	Alpha 株式会社	1998	IT	120	東京	千代田区 1-1	03-1111-2222	info@alpha.jp	180,000,000	30,000,000	4.5
2	Beta 株式会社	2005	製造	80	大阪	北区 2-2	06-3333-4444	contact@beta.jp	110,000,000	15,000,000	4.1

例えば、上記のテーブルへの検索 SQL で遅延が発生している場合、検索で使用する列のみを以下のように切り分けて別テーブルとすることで、SQL 文がアクセスするデータ量を減らすことができる。

会社 1 テーブル:

会社 ID	会社名	設立年	業種	従業員数
1	Alpha 株式会社	1998	IT	120
2	Beta 株式会社	2005	製造	80

会社 2 テーブル:

会社 ID	都道府県	住所	電話番号	メールアドレス	年間売上	利益	評価
1	東京	千代田区 1-1	03-1111-2222	info@alpha.jp	180,000,000	30,000,000	4.5
2	大阪	北区 2-2	06-3333-4444	contact@beta.jp	110,000,000	15,000,000	4.1

この分割はパフォーマンス向上が可能であると同時に無損失分解であるので、場合によっては元の単一の会社テーブルへ復元することも可能。

しかし、水平分割と同様、垂直分割も分割することに論理的な意味を持たないので、原則利用するべきではない。

また、垂直分割は「集約」で代替が可能である。

集約

テーブル分割の代替案に位置づけられる方法として集約がある。

以下の 2 種類に分けられる。

列の絞り込み
サマリテーブル

列の絞り込み

列の絞り込みは、単純にテーブルの列を絞った新規テーブルを作成する方法。

垂直分割とは違い、テーブルを分割せず、元のテーブルはそのまま保持したまま完全に新規のテーブルを作成する。

会社テーブル:

会社 ID	会社名	設立年	業種	従業員数	都道府県	住所	電話番号	メールアドレス	年間売上	利益	評価
1	Alpha 株式会社	1998	IT	120	東京	千代田区 1-1	03-1111-2222	info@alpha.jp	180,000,000	30,000,000	4.5
2	Beta 株式会社	2005	製造	80	大阪	北区 2-2	06-3333-4444	contact@beta.jp	110,000,000	15,000,000	4.1

列を絞って新規に作成する会社テーブル:

会社 ID	会社名	設立年	業種	従業員数
1	Alpha 株式会社	1998	IT	120
2	Beta 株式会社	2005	製造	80

このようにして作成されるオリジナルから縮小された小規模なテーブルをデータマートと呼び、以下のような特徴がある

オリジナルのテーブルを意味的に破壊することなくパフォーマンスも向上させられる
オリジナルのテーブルが更新された際にデータマートも更新される必要があるので、その更新頻度やタイミングを調整しないと更新処理時に負荷が上がり、パフォーマンスが悪化する可能性がある
多くの場合は一日一回〜数回のバッチ更新が多い

サマリテーブル

サマリテーブルは、列の絞り込みと違い、集約関数によってレコードを集計した状態で保持するテーブルを作成する方法。

社員テーブル:

社員 ID	会社 ID	氏名	年齢
1	1	佐藤太郎	25
2	1	鈴木花子	30
3	1	田中健	35
4	2	高橋愛	28
5	2	伊藤誠	40

上記のテーブルを以下の SQL で集計したサマリテーブルを新規に作成する。

SELECT 会社ID, AVG(年齢) AS 平均年齢
FROM 社員テーブル
GROUP BY 会社ID

サマリテーブル:

会社 ID	平均年齢
1	30.0
2	34.0

この方法で新規テーブルを作成することで処理時間が長い集計関数を毎回介することなく、シンプルな SQL で「会社ごとの平均年齢」を取得する要件が満たせ、I/O コストを大きく削減することができる。

しかし、この方法においても前項のデータマートと同様、データ同期の更新頻度・タイミングの問題を抱える。

不適切なキー

以下のようなキー列を考える(これらはほとんどの場合、重なり合っていることが多い)。

主キー、外部キーなどデータベースの機能で設定されるもの
テーブルの結合条件で使用される列(結合キー)

以下の理由から、この二つの用途で使用されるキー列に対して、可変長文字列(VARCHAR)型を使用してはならない。

可変長文字列の列は、キーが満たすべき条件である不変性を備えていない。
- 可変長文字列の使用に適しているのは「名前」などの文字数が固定されていない列
- 「名前」などのデータは長期的なスパンで考えれば変動する可能性が高いので、キーに設定しているとその度にデータの更新処理が必要となってしまうので、キーには向いていない
固定長文字列のキーと比較できない
- 固定長文字列と可変長文字列は、同じ文字列を保持しようとしても物理的に同じ「値」にならないことがほとんどなので、単純に比較できない
  - 固定長文字列: テスト <- 足りない文字数分、空白による穴埋めが行われる
  - 可変長文字列: テスト
- 結合したい二つのテーブルでそれぞれ結合キーが固定長文字列、可変長文字列として設定されていると上記のように比較できない

以上の理由から、キーに使用する列は固定長文字列の「コード」列が望ましい。

ダブルマスタ

元々、別々のシステムで管理されていた顧客マスタデータベースが、企業が統廃合されることなどから、同じ役割を果たすマスタテーブルが二つ存在する状況が発生するのがダブルマスタである。

顧客マスタ A:

顧客コード	顧客名
C001	田中太郎
C002	佐藤花子
C003	鈴木一郎
C004	高橋美咲

顧客マスタ B:

顧客コード	顧客名
C001	田中太郎
C002	佐藤花子
C003	鈴木一郎
C005	伊藤健太

上記のような、同じ顧客の情報も重複して持っているが、それぞれのテーブルに違うデータも存在する場合、顧客全員の情報を得るためには二つのマスタを結合しなければならない。

完全外部結合する場合のSQL

SELECT
  COALESCE(a.顧客コード, b.顧客コード),
  COALESCE(a.顧客名, b.顧客名)
FROM 顧客マスタA a
FULL OUTER JOIN 顧客マスタB b
  ON a.顧客コード = b.顧客コード;

UNIONを使って結合する場合のSQL

SELECT 顧客コード, 顧客名
FROM 顧客マスタA

UNION

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

結合したテーブル:

顧客コード	顧客名
C001	田中太郎
C002	佐藤花子
C003	鈴木一郎
C004	高橋美咲
C005	伊藤健太

完全外部結合とは

通常の外部結合がマスタにする片方だけのテーブルのレコードを保存するのに対し、両方のテーブルへレコードを保存する。

COALESCE 関数は引数を左から順番に評価し、NULL でない最初の引数を返す関数である。

これによりどちらかのテーブルにしか存在しない顧客のレコードに NULL が生じることを防ぐ。

一方、UNION を使って結合する場合は、単純にテーブルを足し合わせるのみであるので、同じキーで違う値を持つレコードが存在した時はどちらも結合後のテーブルに含まれる。

同じキーだがそれぞれのテーブルで違う値を持つレコードが存在する場合の UNION 結合後のテーブル:

顧客コード	顧客名
C001	田中太郎
C001	田中一郎

参考文献

この記事は以下の情報を参考にして執筆しました。

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