7-1 論理設計の「やってはいけない」
本章では、「アンチパターン」と「バッドノウハウ」である。 実際の現場で、よくない設計が行われてることがあるそうです。ここでは、どんな設計が間違っているのかを紹介する。7-2 非スカラ値（第1正規形未満）
配列型による非スカラ値
子列が非スカラ値である。これは表であってリレーショナルデータベースにおけるテーブルではない。 SQLは数年日ど改訂が行われ、最近では、配列型を定義することが、可能になった。つまり 非スカラ値を含むテーブルを設計しているということである。| 商品ID | 商品名 | 商品カテゴリ |
|---|---|---|
| 1 | ぶどう | 果物 |
| 2 | レモン | 果物 |
| 3 | にんじん |
スカラ値の基準は何か？
スカラー値の基準は何でしょうか?ー端的に話せば、「分解不可能な値と判断できるもの」。例えば、「A商事」の値があり、「A」+「商事」で分けても、商事は意味を持つけど、Aは意味を成さない。
では、「斉藤 テスト 」さんという値があった場合はどうなる?
「斉藤」 + 「テスト」に区分してもそれぞれが「氏」 + 「名」でそれぞれだけで意味を成す。
この場合は分けることができる。この際は、テーブルの単位を考慮して分ける必要がある。
では、どのように分ける必要があるのか?
A. 一定の答えはなく、システム要件で考慮する必要がある。しかし著者自身は以下のように考えるようである。
意味的に分解できる限り、なるべく分解して保持するという基本設計が良いと考えるようである。
理由は
分割したものを後で結合するのは簡単なのに対して、結合された状態のものを後から分割するのは大変だからである。
7-3 ダブルミーニング
この列の意味は何でしょう？
ダブルミーニングは言葉通り、同列に複数の意味を持つ値が含まれることを指す。もちろん、このテーブル設計は、ふさわしくない。しかし、作成自体は可能である。テーブル列は「変数」ではない
ダブルミーニングが作られる背景にはプログラミング言語による変数がある。 エンジニアが値を変数に入れるのと同様に、テーブルも値を格納することにより、 **リレーショナルデータベースをダブルミーニングのように扱う。** しか、リレーショナルデータベースにおけるテーブルは、意味の持つ実質的なエンティティの写像、物を映し取ったものであるため、**変数よりも静的で固定的な存在である。**7-4 単一参照テーブル
第三正規化を行った、社員テーブル、部署テーブル、会社名テーブルがあったとします。 そこで、構造が会社と部署で類似していました。下記、テーブル構造です。多すぎるテーブルをまとめたい？
| 会社ID | 会社名 |
| ------ | ------- |
| com1 | 会社名1 |
| com2 | 会社名2 |
| com3 | 会社名3 |
| 部署ID | 部署名 |
| ------ | ------- |
| 部署1 | 部署名1 |
| 部署2 | 部署名2 |
| 部署3 | 部署名3 |
上記は同じ構造になっており、一つにまとめることができる。これを単一参照テーブルと呼ぶ。
単一参照テーブルの功罪
単一参照モデルのメリットと欠点 メリット マスターテーブルの数が減るためER図やスキーマがシンプルになる コード検索のSQLを共通化できる デメリット コードタイプやコード長、コード内容など、必要とされる列長はコード体系によって異なるため余裕を見てかなり大きめの可変長型文字列で指定しなければならない。一つのテーブルにレコードを集約するためにこど体系の種類と多さによってかなり検索のパフォーマンスが悪化する。
コード検索検索のSQLで指定したコード値などにミスがあってもエラーが生じないため原因に気付きにくい。
ER図がすっきりするといってもER図の可読性を欠いており、ER図は読みにくい。
7-5 テーブル分割
テーブル分割である。テーブル分割はそれほどバッドノウハウにはならないが、場合によっては良くないかたちになるため注意が必要である。テーブル分割の種類
テーブル分割は大きく分けて2つある。 1. 水平分割 1. 垂直分割水平分割
水平分割はレコードベースで分割するものである。 | 年度 | 会社コード | 売上(万円) |
| ---- | ---------- | ---------- |
| 2001 | C0001 | 50 |
| 2001 | C0003 | 52 |
| 2002 | C0001 | 42 |
| 2002 | C0003 | 59 |
| 2002 | C0003 | 70 |
このテーブルだと行数が少ないが、実際のテーブルはレコード数が何百万~何千万レコードになる。その結果
検索パフォーマンスが著しく悪くなる。理由は、検索パフォーマンスの最大な要因は
ストレージに対するI/Oコストの増大だからである。
そこで年度ごとにテーブルを分割すればレコード数が減少に解決につながる、、、
| 年度 | 会社コード | 売上(万円) |
| ---- | ---------- | ---------- |
| 2001 | C0001 | 50 |
| 2001 | C0003 | 52 |
| 年度 | 会社コード | 売上(万円) |
| ---- | ---------- | ---------- |
| 2002 | C0001 | 42 |
| 2002 | C0003 | 59 |
| 2002 | C0003 | 70 |
こうすれば、レコード数は少なくなり、検索しやすくなる。
しかし、ここには3つの落とし穴がある。
デメリット1:分割する意味がない
これは全てのテーブルに共通することですが、正規化する理由がないものに分割する理由がないということ.
つまり、正規化する必要がなければ、分割する必要がないということである。
デメリット2:拡張性に乏しい
この分割により検索パフォーマンスが最適化する場合は、年度ごとに検索する場合のみである。
言い換えれば、ある期間からある期間の売上推移などを検索したい場合はこの分割によるパフォーマンスはむしろ悪化する。→理由は結合しなければいけないからである。
デメリット3:他の代替案
パーティーションキー
垂直分割
垂直分割は列を軸に分割する。集約
集約はテーブル分割の代替案として用いられる。２種類ある。 1. 列の絞り込む 1. サマりテーブル列の絞り込み
列の絞り込みは、主に検索カラムとして使われるカラムを集約したテーブルを分割ではなく、別のテーブルとして用意する。
以下のような例である。
社員テーブル
会社コード,社員ID,年齢を検索対象とする
社員 (元テーブル)
| 会社コード | 社員Id | 社員名 | 年齢 | 部署コード |
| ---------- | ------ | ------ | ---- | ---------- |
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 橋本 | 32 | D02 |
| C0002 | 001F | 三島 | 40 | D01 |
| C0002 | 000A | 斉藤 | 50 | D03 |
社員(データマート)
| 会社コード | 社員ID | 年齢 |
| ---------- | ------ | ---- |
| C0001 | 000A | 40 |
| C0001 | 000B | 32 |
| C0002 | 001F | 40 |
| C0002 | 000A | 50 |
このデータマートは非常に便利。元のデータ、テーブルを破壊することなく、パフォーマンスも向上させることができるので、実際の開発においてもよく用いられる。便利すぎて、データマーが多く作成され、ストレージが圧迫することもある。また、別の点でも注意が必要である。データ同期の問題である。
具体的に言えば、マートの更新タイミングである。マートとデフォルトのテーブルは同期しなければならない。
例えば、コード上でマート テーブルの年齢が更新された場合でも、デフォルトテーブルが更新されていなければ、
データの齟齬が起きる。であれば、更新タイミングの間隔を短くすルコとが思いつくだろう。解消されるがデータ負荷が著しくかか本来の目的が達成できなくなる。つまり、更新処理の負荷を軽減させるのではなく、負荷がかかる。
そこで、ベストな更新タイミングが本書で言えば、多くの場合 「1~数回/日だそうである。」
しかしこれにはシステム設計、要件によって異なるため、臨機応変に対応しなければならない。
サマリデータについて
サマリテーブルも集約手段の一つ。サマリテーブルは集約関数によって集約した状態で保持する。
例えば、会社別に社員の平均年齢を求めたいとします。
毎回、求めたい社員テーブルにSELECT文でアクセスし、社員の平均年齢を求めても良いですが
テーブルの規模が大きくなるとその集約コストが大きくなり実行時間は長くなる。
そこで以下のように、事前に集約データを作っておく。
社員 (元テーブル)
| 会社コード | 社員Id | 社員名 | 年齢 | 部署コード |
| ---------- | ------ | ------ | ---- | ---------- |
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 橋本 | 32 | D02 |
| C0002 | 001F | 三島 | 40 | D01 |
| C0002 | 000A | 斉藤 | 50 | D03 |
社員平均年齢
| 会社コード | 平均年齢 |
| ---------- | -------- |
| C0001 | 41 |
| C0002 | 35 |
社員平均年齢テーブルサイズは行列ともに元の社員テーブルより行列が少なくなり、I/Oコストの削減も大きく果たすことができる。
また、オリジナルのテーブルを変更することもないため、分割によるデメリットもありません。
ただ、先ほどのデータマートと同じように更新タイミングによるデメリットが生じる。タイミングのスパンが短ければ、齟齬が生じる時間帯が短くできるが、そのぶん負荷が大きく関わるためデメリットが生じる。
また、一般的にサマリデータは元のテーブルより小さくなるものの、新たにテーブルを追加するために
ストレージの容量を消費するのも事実である。
7-6 不適切なキー
データベースでテーブルを作る前に考える要素の一つとして「どの列にどのようなデータ型を採用するか」という問題である。●文字列
●数値
●日時
これ以外にも、DBMSは標準的なものから独自仕様のものまで、多くのデータ型を持っている。それ以外のデータ型についても、「データ型属性に応じて採用していくことにもなるが、中には特定の目的のために佩用し難いデータ型もある。」
キーは永遠に不変です！
可変長文字列型に適用できるデータは具体的などのようなデータでしょうか? ーA.何かの名前です。 EX.氏名です。 結婚すると姓が変わることがあります。名前は固定値ではありませんので可変長文字列であるのは自然なこと。
しかし、名前をキーに使うことは危険です。上記のように、名前はコロコロ変わるが故に、データを扱うことが難しくなりますし、テーブル構造もコロコロ変わってはデータの更新処理が多くなる。
データの更新処理が多くなると、システムの安定的な運用とパフォーマンスの側面でマイナスになる。
同じデータを意味するキーは同じデータ型にすべし
固定帳文字列と可変長文字列とでは、同じ文字列を保持する場合でも、**物理的には同じ文字列にはならない。** 固定長文字列が空白による穴埋め(バディング)するから。● 固定帳文字列: 'テスト ＋[スペース]'
● 可変長文字列: 'テスト'
そのため、ある結合において、片方が固定長文字列でもう片方が、可変長文字列の場合はアンマッチになる。
7-7 ダブルマスタ
バッドノウハウの一つはダブルマスタである。著者によると、著者の造語であるようだ。 この意味は「マスタテーブルが二つ存在すること」。ダブルマスタはSQLを複雑にし、パフォーマンスを悪化させる
同じデータを持っているテーブルが2つ存在しているが、片方にはあるデータAが、もう片方にはデータAがない場合に **UNION**や**完全外部結合**すると、いずれもコストが高い処理であり、パフォーマンスが悪くなる。 ::: note 完全外部結合は、通常の外部結合がマスタにする片方のテーブルのレコードを保存するのに対し、 双方のテーブルに保存する特徴がある。 :::ダブルマスタはなぜ生じるのか
ダブルマスタは一般的な設計を行えば起こりにくい。しかし、理由が一つある。理由:システムが統合せざるを得ない状況下である。例えば、金融機関と小売企業が経営統合する必要が出てきた際、「顧客の一元管理」によりシステムも統合する必要がある。互いに同じような**役割**を持っていた時、同等のテーブルが複数存在することになる。その時きちんとデータ統廃合を行えば、ダブルマスタは生じないがそれには時間と人手が生じる。
この手間を省くと、ダブルマスタは生じる。