DB設計基礎講座 #2 アンチパターン

バッドノウハウ

設計の際に避けるべきノウハウを列挙する

配列型

その名の通り、あるカラムを配列とできる
ただし非スカラ値を含むテーブルとなるため、第一正規形でなくなる
(スカラ値 = 分解不可能な値)
基本的に配列は行で表現するべき

単一参照テーブル

同じ構造を持っている別テーブルを１つにまとめたテーブル。
例えばIDカラムと名称カラムを持つようなマスタテーブルは、正規化を行うことで複数存在することになるが、これをまとめたもの。
どのマスタの情報かを表す「コードタイプ」列を追加し、残りは元のテーブルの構造を引き継ぐ。

メリット

• マスタテーブルの数が減る
• コード検索のSQLを共通化

デメリット

• 各列の必要とされる列長はコード体系によって異なるため、余裕をもってかなり大きめの可変長文字列で宣言する
• 場合によってはレコード数が多くなり、検索パフォーマンスが悪化する
• SQL内のコードタイプなどにミスがあってもエラーにならないため、バグが混入しやすい
• ER図の可読性を下げる

テーブル分割

水平分割

レコード単位にテーブルを分割する手法。
例えば売上テーブルを1年ごとに分割するなど。
ただしいくつかの重大な欠点があるため、RDBでは禁止されている。

1. 分割する意味的な理由がない : あくまでもパフォーマンス改善のみが理由
2. 拡張性に乏しい : 全年のサマリを得ることが困難であったり、アプリケーションの改修が都度必要
3. 他の手段が存在する : パーティション

パーティション

テーブルを分割することなく、パーティションキーを軸として物理的に格納領域を分離する。
これによってSQLがアクセスするデータ量を1/nに減らせる。
一般的にはパーティションはインデックスよりもカーディナリティが大きく、かつ値の変更があまり起きない列をキーにして利用する。
年や都道府県などカーディナリティが10 ~ 50程度であるキーが対象となる。
インデックスの場合は、もう少しカーディナリティが高くないと、あまり絞り込み効果が得られない。

垂直分割

列を軸に分割する手法。
よく利用される列とそれ以外の列で分割を行うことで、SQL文がアクセスするデータ量を減らす。
ただし分割することが論理的な意味を持たないという欠点があるため、原則利用するべきではない。
集約で代替が可能である。

集約

1. 列の絞り込み
   
   頻繁に参照される列のみを持った新しいテーブルを追加作成する。
   オリジナルのテーブルを残すため分割ではない。
   このようにして作られる(オリジナルのテーブルと比較すれば)小規模なテーブルを「データマート」、あるいは省略して「マート」と呼ぶ。
   ストレージ圧迫とデータ同期(整合性)問題がつきまとう。

2. サマリテーブル
   
   テーブルの規模が大きくなると集約処理にコストが大きくなるので、事前に集約関数によってレコードを集約した状態でテーブルを作っておき、アクセスするときのI/Oコストを大きく削減する。
   オリジナルのテーブルを変更することも無いので、分割によるデメリットは無い。
   例えば平均テーブルなどで、平均年齢や平均年収なんかを保持しておく。
   デメリットはパーティションと同様で、ストレージ圧迫とデータ同期(整合性)問題。

不適切なキー

キーとは主キー、外部キー、結合キーのことを指す。
明らかに使ってはいけないのは可変長文字列。
キーが満たすべき不変性を備えていないため。例えば名称は変更の可能性がある。
また固定長文字列との混同があるため。固定長文字列は空白による穴埋め(パディング)をするが、可変長文字列では空白での穴埋めをしないため、列同士を比較するとアンマッチしてしまう。
また数値型も避けれるなら避けるべき。固定長文字列の001と数値型の1は一致しない。
そのためできる限りは固定長文字列をキーとすることが望ましい。

グレーノウハウ

なるべき避けるべきノウハウ
ただしメリットがデメリットを上回る場合は使用することもある

代理(サロゲート)キー

自然キー(入力データに最初から存在しているデータから選ばれたキー)から主キーが決められない場合に用いるキーで、代理として人工的にテーブルに追加するキー
原則としては極力代理キーの使用は避けて、自然キーによる解決を図るべき

自然キーによる解決

基本的には自然キーによって解決を図りたい
その場合に有効となる手段を紹介する

1. タイムスタンプ
   ある時点を意味する情報
   例えば年や月ごとにテーブルレコードののスナップショットを取ることで可能
   それぞれの断面が履歴の役割となる
   連番という意味のないデータではなく、年度などは業務的に意味を持つ列(自然キー)
   ただしスナップショットを取るタイミングで、その瞬間のフルセットを取得する必要があるので、レコード数が増える

2. インターバル
   データの有効な期間を表す情報。
   開始年度と終了年度という２つの時間列を持つことで、データの期間を表す
   終了時点のデータには、NULLではなくそのカラムが取りうる最大値を指定することが多い
   タイムスタンプに比べデータ量を少なくできる
   ただしSQLの条件がタイムスタンプが時点を指定するのに対し、期間を指定するためBETWEENを使うことになる

代理キーによる解決

代理キーは論理的には不要なキーであるため、論理モデルを複雑にする
代理キーは本来使わなくても何とかなる道具である

代理キーを検討する場合は以下の３パターン

1. 主キーにできるような一意キーが存在しない
   言語両断なケースである。設計が不十分

2. 一意キーはあるが、サイクリック(cyclic)
   ナンバープレートのように0000 ~ 9999までしか使えない値がキーとなる場合
   既に廃止された番号を使い回す形で運用していくことを強いられている
   ナンバープレートが他の車に変化したとしても、その履歴を管理できないが、代理キーがそれを解決する

3. 一意キーはあるが、途中で指す対象が変化する
   A社とB社が合併するが、引き続き名前はA社とするなどの場合
   表向きは継続的にA社だが、社員数に変化があり、その履歴管理をしたい場合に困るが、代理キーがそれを解決する

オートナンバリング

代理キーに求められる要件は、基本的に一意性のみ
そのためオートナンバリングが用いられることが多い
１レコードに一意な整数型の数値を自動的に割り振る

シーケンスオブジェクト

一意な連番を払い出すオブジェクト。ID列に比べても細かい制御ができるため推奨
以下のような様々なオプションを設定できる

• 開始値
• 最大値
• 増分
• サイクリックに採番するか

ロックメカニズムが実装されているため、排他制御などの実装が不要な点がメリット
ただしロックが起こることで、同時アクセスが多数集中した際はボトルネックとなり、性能遅延が発生する可能性がある

ID列

一意な連番を払い出すデータ型。指定できるオプションはシーケンスオブジェクトよりも少ない
またミドルウェア間での移植性が低い

• 開始値
• 増分

アプリケーション側での実装

１行１列の整数型のデータを持つ採番テーブルを利用する方法
他のテーブルのデータを登録する度にインクリメントする
排他制御の仕組みをきちんと備えたプログラム開発とテストが必要なためコストがかかる
またER図が複雑になることもデメリット

列持ち(繰り返し項目)テーブル

配列を表現したい場合に用いられる
例えばある人の子をデータとして持ちたい場合、子供というのは複数存在する可能性がある
そのため列に「子１」、「子２」、「子３」を持ち、表現するようなテーブルを列持ちテーブルと呼ぶ
直感的に理解しやすいため、設計や実装が楽であるという点から、使われることがある
ただし拡張性に乏しい、つまり「子４」という列が欲しい場合、テーブルだけでなくアプリケーション側も修正する必要がある
また子がいない場合は、NULLを用いる必要があるが、NULLは演算などでも厄介な挙動をするため基本的には使いたくない

多段ビュー

ビューに対するビューのこと
複数のSELECT文が内部的に必要となるためパフォーマンスが悪い
またテーブルとビューの依存関係をわかりにくくするため、仕様が複雑になり管理が困難となる
原則としてビューの使用は１段にとどめる