論理設計のグレーゾーン
概要
この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。
この記事はデータベース設計における「バッドノウハウではないけど、用法要領をよく考えて使用する必要がある」グレーゾーンの設計プラクティスについて解説しています。
主キーが役に立たない時の代理キー
リレーショナルデータベースのテーブルにおいては、原則、主キーが必ず必要であるが、以下のように主キーを簡単には決めることができないケースが存在する。
パターン 1: そもそも入力データに主キーにできるような一意キーが存在しない
例えば、入力インターフェースではレコードの二重登録を許容していて、後でレコードの重複を排除する処理が行われるような業務システムの場合、登録時点では一意なキーが存在しないので主キーが決められない。
パターン 2: 一意キーではあるが、サイクリックに使い回される
これは、主キーの値が全て使われてしまった場合に、既存の値が使い回されるケース。
例えば、以下のようにCITY-数字４桁という桁数が固定されたフォーマットの主キーの場合、CITY-9999まで値を使い切ったら既存の主キーで過去に使用されなくなったものを使い回してレコードを登録する場合がある。
市町村テーブル:
| 市町村コード | 市町村名 | 人口 |
|---|---|---|
| CITY-1000 | さくら市 | 120000 |
| CITY-2000 | みどり市 | 85000 |
| CITY-3000 | ひかり市 | 45000 |
↓
CITY-2000 が使い回されて使用(みどり市は過去に廃止):
| 市町村コード | 市町村名 | 人口 |
|---|---|---|
| CITY-1000 | さくら市 | 120000 |
| CITY-2000 | あおば町 | 31000 |
| CITY-3000 | ひかり市 | 45000 |
このような運用の場合、みどり市のような過去に同じ主キーを使用していて廃止された市町村のデータは履歴が残らなくなってしまうので、過去の履歴をたどりたい場合はこの主キーは役に立たない。
パターン 3: 一意キーはあるが、途中で指す対象が変化する
こちらはパターン 2 と似ているが、理由が値の枯渇ではなく、例えば以下のように市町村データが途中で合併されるようなケース。
市町村テーブル:
| 市町村コード | 市町村名 | 人口 |
|---|---|---|
| CITY-1000 | さくら市 | 120000 |
| CITY-2000 | みどり市 | 85000 |
| CITY-3000 | ひかり市 | 45000 |
↓
さくら市とみどり市が合併:
| 市町村コード | 市町村名 | 人口 |
|---|---|---|
| CITY-1000 | さくら市 | 205000 |
| CITY-3000 | ひかり市 | 45000 |
本来であれば、この場合も合併前と合併後の情報は分けて管理したいが、このコード体系ではそれが難しい。
代理キーによる解決
前項までに挙げたパターンを解決するために、入力データに最初から存在しているキー(自然キー)の「代理」として新たに追加するキーを代理キーと呼ぶ。
以下のように、一意な連番となる管理コードを追加することでパターン 2 のようにコードに重複が発生しても問題がなくなる(パターン 3 も同様の形式で解決できる)。
代理キー(市町村管理コード)を追加した市町村テーブル:
| 市町村管理コード | 市町村コード | 市町村名 | 人口 |
|---|---|---|---|
| 0 | CITY-1000 | さくら市 | 120000 |
| 1 | CITY-2000 | みどり市 | 85000 |
| 2 | CITY-3000 | ひかり市 | 45000 |
↓
新しい市町村を追加:
| 市町村管理コード | 市町村コード | 市町村名 | 人口 |
|---|---|---|---|
| 0 | CITY-1000 | さくら市 | 118000 |
| 1 | CITY-2000 | みどり市 | 85000 |
| 2 | CITY-3000 | ひかり市 | 45500 |
| 3 | CITY-2000 | あおば町 | 31000 |
このように、人工的なキーをシステム側で付与することで、自然キーを主キーに選んだ場合に発生する不都合を解消できる。
しかし、以下の理由により、原則として、極力代理キーの使用は避けて、自然キーによる解決を図るべきである。
- 代理キーは論理的には不要なキーであるため、論理モデルをわかりづらくしてしまう
- ER 図を見ても代理キーが何の役割を果たしているかわからない
- そもそも代理キーは本来「使わなくても何とかなる」道具
自然キーによる解決
代理キーを使用せずに自然キーのみで上で挙げた３つのパターンを解決する方法を考える。
-
パターン 1
- 自然キーの中に主キーが存在しないケースなので、データベース側で解決する手段はない。
- 業務仕様を調整、あるいはデータベースに投入される前にアプリケーション側でデータが一意となるように整形する対策を行うしかない。
-
パターン 2 & パターン 3
- 履歴管理のための時間を表す列を追加する
- タイムスタンプもしくはインターバルという手法があるので以下で解説する。
タイムスタンプ
以下のようにタイムスタンプを表す列(年度)を追加して、年度ごとにそのタイミングにおける市町村のレコード集合を登録する(スナップショットを年度ごとに用意)。
これにより、2023年度と2024年度で市町村コードCITY-2000に割り振られた市町村が変更されている履歴を調べることが可能となる。
パターン 2 に追加した例:
| 市町村コード | 年度 | 市町村名 | 人口 |
|---|---|---|---|
| CITY-1000 | 2023 | さくら市 | 120000 |
| CITY-2000 | 2023 | みどり市 | 85000 |
| CITY-3000 | 2023 | ひかり市 | 45000 |
| CITY-1000 | 2024 | さくら市 | 118000 |
| CITY-2000 | 2024 | あおば町 | 31000 |
| CITY-3000 | 2024 | ひかり市 | 45500 |
SELECT 市町村コード, 市町村名
FROM 市町村
WHERE 年度 = 2023;
この方式は、データの形式が単純なため、SQLの条件が簡単になるというメリットがある一方、必ず一つのスナップショットに含まれる市町村をフルセットで保持しなければならないのでレコード数が膨大となる可能性がある。
インターバル
先ほどのタイムスタンプがある「時点」を意味したのに対し、データの有効な「期間」を表す。
これにより、タイムスタンプと同様に市町村データの変更履歴が確認できるだけでなく、タイムスタンプと違い年度ごとのスナップショットを用意する必要がなく、市町村が変更された場合は終了年度列をその年度へ更新して、次の年度を開始年度に設定した新しいレコードを一つ作成するのみで更新が可能。
パターン 2 に追加した例:
| 市町村コード | 開始年度 | 終了年度 | 市町村名 | 人口 |
|---|---|---|---|---|
| CITY-1000 | 2020 | 9999 | さくら市 | 120000 |
| CITY-2000 | 2015 | 2023 | みどり市 | 85000 |
| CITY-2000 | 2024 | 9999 | あおば町 | 31000 |
| CITY-3000 | 2010 | 9999 | ひかり市 | 45000 |
SELECT 市町村コード, 市町村名
FROM 市町村
WHERE 2023 BETWEEN 開始年度 AND 終了年度;
オートナンバリング
「可能な限り自然キーを使う」という原則に従うべきであるが、どうしても自然キーが使えない場合にやむをえず代理キーを使用する場合を考える。
その際、よく利用されるのが、1 レコードに一意な(通常は整数型の)数値を自動的に割り振るオートナンバリングという手法。
オートナンバリングの例(代理キーは市町村管理コード):
| 市町村管理コード | 市町村コード | 市町村名 | 人口 |
|---|---|---|---|
| 0 | CITY-1000 | さくら市 | 118000 |
| 1 | CITY-2000 | みどり市 | 85000 |
| 2 | CITY-3000 | ひかり市 | 45500 |
| 3 | CITY-2000 | あおば町 | 31000 |
この手法によって数値を払い出すためには、主キーとして使用する際に以下の要件が守られなければならない。
- 重複値が生じないこと(一意性の保証)
- 歯抜けが生じないこと(連続性の保証)
- 厳密に連番である必要があるかは業務要件次第
これらの要件をクリアするには、データベース機能を利用する方法とアプリケーション側で実装する方法がある。
・データベース機能でオートナンバリングを実現する方法
-
シーケンスオブジェクト
- アクセスすることによって一意な連番を払い出すオブジェクト
- SELECT 文でアクセスすることで重複のない連番を取得することが可能
- 開始値、最大値、カウントアップの増分などの条件をオプションで指定可能
- 比較的新しい機能なので、まだ全ての DBMS でサポートされているわけではない(MySQL では使用できない)
- 同時アクセスが多数集中すると、この連番払い出しがボトルネックになる可能性がある
シーケンスオブジェクトの使用例CREATE SEQUENCE test_seq -- シーケンスオブジェクトの作成 START WITH 1; -- 開始値を 1 に設定 SELECT nextval('test_seq'); -- シーケンスオブジェクトから次の値を取得 -
ID 列
- 一意な連番を払い出すデータ型
- テーブルで使用する主キーとしてこのデータ型を採用する(主キー以外でも使用自体は可能)
- 指定可能なオプションはシーケンスオブジェクトに比べると少なく、開始値と増分くらい
- DBMS ごとに実装が統一されておらず、移植性が低い
・アプリケーション側の実装でオートナンバリングを実現する方法
オートナンバリングをアプリケーション側で実装する最もポピュラーな方法が、「採番テーブル」を利用する方法。
一行一列の整数型のデータを持つ採番テーブルを用意し、他のテーブルにデータを登録するたびにインクリメントしていく。
採番テーブル:
| シーケンス |
|---|
| 4 |
↓ 新しい番号を採番
| 市町村管理コード | 市町村コード | 市町村名 | 人口 |
|---|---|---|---|
| 0 | CITY-1000 | さくら市 | 118000 |
| 1 | CITY-2000 | みどり市 | 85000 |
| 2 | CITY-3000 | ひかり市 | 45500 |
| 3 | CITY-2000 | あおば町 | 31000 |
| CITY-9999 | しんき町 | 100000 |
この方法には以下のようなデメリットがあり、データベースの機能でオートナンバリングを実現可能なため、あえてアプリケーション側で実装する必要はない。
- この機能を持ったプログラムとテストを用意するための実装コスト
- 採番テーブルと連番を使用するテーブルは密接に関連しており、片方が欠けたらシステムは動かないが、ER図からそのような依存関係がわからない
- 二人以上が同時に採番テーブルへアクセスした場合に、同じ番号を引き当ててしまうと使用するテーブル側で一意性制約違反が発生するので、適切な排他制御を実装する必要がある
列持ちテーブル
リレーショナルデータベースにおいて、配列型の使用は基本的にはバッドノウハウであるが、配列型を使用せずに配列を模倣する論理設計として「列持ちテーブル」という手法がある。
例えば、プログラミング言語においては、社員ごとの子供のデータを配列で保持する(kodomo[0], kodomo[1], kodomo[2]...)が、それをテーブルの列で模したのが、列持ちテーブルである。
列持ちテーブルの例:
| 社員ID | 社員名 | 子供名1 | 子供名2 | 子供名3 |
|---|---|---|---|---|
| 1001 | 佐藤 太郎 | 佐藤 花子 | 佐藤 次郎 | NULL |
| 1002 | 鈴木 一郎 | 鈴木 美咲 | NULL | NULL |
| 1003 | 高橋 健 | NULL | NULL | NULL |
列持ちテーブルのメリット・デメリット
メリット:
-
シンプルな設計
- 正規化やスカラ値の概念を理解していない人でもテーブルを見れば
子供名1、子供名2といった列が配列を表現していることがわかる
- 正規化やスカラ値の概念を理解していない人でもテーブルを見れば
-
入出力のフォーマットと合わせやすい
- シンプルゆえに、アプリケーションサイドとのインターフェース設計も簡単
- 実際に業務で使用される帳票でも、被扶養者の情報を列として並べるフォーマットがあるので、その場合はSELECT結果をそのまま出力できる
デメリット:
-
列の増減が難しい
- 列数が足りなくなった時に列を追加した場合、アプリケーション側の修正も必要となるので変更コストが高い
-
無駄な NULL 値が発生する
- 子供がいない社員の場合、子供名1～3の全ての列がNULLとなる
- NULL が演算に含まれていると SQL の結果を混乱させる一因となる
行持ちテーブル
列持ちテーブルは利点と欠点が拮抗しているので、使い所によっては有用だが、基本的には避けるべき設計手法である。
基本的には、以下のような「行持ち」のテーブル構成を採用するべき。
| 社員ID | 枝番 | 子供名 |
|---|---|---|
| 1001 | 1 | 佐藤 花子 |
| 1001 | 2 | 佐藤 次郎 |
| 1002 | 1 | 鈴木 美咲 |
また、列持ちテーブルと行持ちテーブルは SQL で簡単に相互変換が可能である。
-- 列持ちテーブルから行持ちテーブルへ変換するSQL
SELECT 社員ID, 1, 子供名1
FROM 扶養者_列持ち
WHERE 子供名1 IS NOT NULL
UNION ALL
SELECT 社員ID, 2, 子供名2
FROM 扶養者_列持ち
WHERE 子供名2 IS NOT NULL
UNION ALL
SELECT 社員ID, 3, 子供名3
FROM 扶養者_列持ち
WHERE 子供名3 IS NOT NULL
-- 行持ちテーブルから列持ちテーブルへ変換するSQL
-- CASE 式は行を列に変換するための常套手段
SELECT
社員ID,
MAX(CASE WHEN 枝番 = 1 THEN 子供名 ELSE NULL END),
MAX(CASE WHEN 枝番 = 2 THEN 子供名 ELSE NULL END),
MAX(CASE WHEN 枝番 = 3 THEN 子供名 ELSE NULL END)
FROM 扶養者_行持ち
GROUP BY 社員ID;
このように、行持ちと列持ちの変換は SQL で簡単に可能なので、最初は拡張性の高い行持ちテーブルで設計しておき、パフォーマンス上の問題でどうしても必要になった場合に列持ちテーブルへ変換する方針が良い。
アドホックな集計キー
以下のような都道府県テーブルを考える。
都道府県テーブル:
| 県コード | 県名 | 人口（人） |
|---|---|---|
| 01 | 北海道 | 5,140,000 |
| 02 | 青森県 | 1,200,000 |
| 03 | 岩手県 | 1,210,000 |
| 04 | 宮城県 | 2,300,000 |
| 05 | 秋田県 | 950,000 |
| 06 | 山形県 | 1,060,000 |
| 07 | 福島県 | 1,800,000 |
| 08 | 茨城県 | 2,860,000 |
| 09 | 栃木県 | 1,930,000 |
| 10 | 群馬県 | 1,940,000 |
上記テーブルで例えば地方ごとに人口の合計を求めたい場合、集約関数とGROUP BY 句を使用したいが、地方を表す列が存在しないため、SQLで直接集計することができない。
そのような場合、一番安直な解決策としては以下のように「地方コード」列を都道府県テーブルに追加する方法。
都道府県テーブル（地方コード追加後）:
| 県コード | 県名 | 人口（人） | 地方コード |
|---|---|---|---|
| 01 | 北海道 | 5,140,000 | 1 |
| 02 | 青森県 | 1,200,000 | 2 |
| 03 | 岩手県 | 1,210,000 | 2 |
| 04 | 宮城県 | 2,300,000 | 2 |
| 05 | 秋田県 | 950,000 | 2 |
| 06 | 山形県 | 1,060,000 | 2 |
| 07 | 福島県 | 1,800,000 | 2 |
| 08 | 茨城県 | 2,860,000 | 3 |
| 09 | 栃木県 | 1,930,000 | 3 |
| 10 | 群馬県 | 1,940,000 | 3 |
この方法は、簡単に SQL で集計可能になるが、以下のようなデメリットが生まれる。
- こうしたアドホック(場当たり的な)キーは、コード体系が短いスパンで変わったり、別のコード体系が必要になったりする
- アドホックキーを、変更のたびにサイズの大きいテーブルへ次から次へと追加すると、テーブルがますます巨大になり、パフォーマンスが悪化する
このため、以下のような別の解決策が考えられる。
解決策 1: アドホックキーを別テーブルで管理する
都道府県テーブルに直接アドホックキーを追加するのではなく、地方コードを管理する別テーブルを用意する。
地方テーブル:
| 県コード | 県名 | 地方コード | 地方名 |
|---|---|---|---|
| 01 | 北海道 | 1 | 北海道地方 |
| 02 | 青森県 | 2 | 東北地方 |
| 08 | 茨城県 | 3 | 関東地方 |
この方法は、レコード数がトランザクションテーブルに比べて非常に少ないため、メンテナンスが容易となる。
一方で、SQLで集計する際にJOINが必要となるため、パフォーマンス問題の解決とはならない。
解決策 2: ビューを使用する
オリジナルのテーブルに手を加えずに、地方コードを追加したビューを用意すれば、そのビューへのアクセスによってSQLで集計が可能となる。
こちらは、実質的にオリジナルのテーブルへアクセスしているのとコストは変わらないため、必要なコードの数だけビューを作っても、パフォーマンスが悪化することはない。
ビュー
SQLの検索結果に名前を付け、テーブルのように扱える仮想テーブル。
解決策 3: GROUP BY 句でアドホックキーを作る
SQLの GROUP BY 句で CASE 式を使用してコードの読み替えを行う。
SELECT
CASE
WHEN 県コード = '01' THEN 1
WHEN 県コード BETWEEN '02' AND '07' THEN 2
WHEN 県コード BETWEEN '08' AND '14' THEN 3
ELSE NULL
END AS 地方コード,
SUM(人口) AS 地方別人口合計
FROM 都道府県
GROUP BY
CASE
WHEN 県コード = '01' THEN 1
WHEN 県コード BETWEEN '02' AND '07' THEN 2
WHEN 県コード BETWEEN '08' AND '14' THEN 3
ELSE NULL
END;
CASE 式は式の仲間なので、以下のような句ならどこでも書くことが可能。
- SELECT 句
- WHERE 句
- GROUP BY 句
- HAVING 句
- ORDER BY 句
多段ビュー
ビューへのアクセス
ビューはテーブルと違い、実際のデータを保持せず、物理的には、 SELECT 文が書かれたファイルにしか過ぎない。
つまり、ビューへのアクセスは、以下のようなイメージでオリジナルのテーブル(基底テーブル)へアクセスしている。
ユーザー / アプリ
|
| SELECT
v
【 ビュー 】
（定義されたSELECT文）
|
| SELECT
v
【 実テーブル群 】
users / orders / ...
これは 2 段階の SQL が発行、もしくはマージされて実行されるが、いずれにせよオリジナルテーブル(基底テーブル)一つの SQL 文でアクセスするよりは複雑で高コストな処理となる。
多段ビューの問題点
前提として、ビューは基底テーブルを隠蔽する技術であるが、本当に基底テーブルを意識しなくて良いのはエンドユーザーのみで、エンジニアはビューの背後にあるテーブルの存在を常に意識していなくてはならない。
このことを意識せずに、以下のように多段でビューを構成するのが多段ビューである。
ユーザー / アプリ
|
| SELECT
v
【 VIEW_A 】
|
| SELECT
v
【 VIEW_B 】
|
| SELECT
v
【 VIEW_C 】
|
| SELECT
v
【 実テーブル群 】
users / orders / ...
このような多段ビューの問題点は以下の通り。
- 多段階層のビューへアクセスする場合、幾つもの SQL 文が実行されるため、パフォーマンスが低下する
- テーブルとビューの依存関係がわかりにくくなり、仕様が複雑になり管理が困難になる
このため、ビューの使用は原則として一段にとどめておく。
参考文献
この記事は以下の情報を参考にして執筆しました。