# 07. DB Design
________________________________________
達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ  
https://www.shoeisha.co.jp/book/detail/9784798124704

失敗から学ぶRDBの正しい歩き方 1刷  
https://gihyo.jp/book/2019/978-4-297-10408-5  
(電子書籍は1刷。未改善正誤表あり)

SQLアンチパターン 10刷  
https://www.oreilly.co.jp/books/9784873115894/

リレーショナル・データベースの世界  
http://mickindex.sakura.ne.jp/database/idx_database.html

セルコ・セレクション  
http://mickindex.sakura.ne.jp/database/celko/idx_celko.html

```text
この記事では、以下のセオリーを無視している

・IDリクワイアド（とりあえずID）：SQLアンチパターン　より
理由：ID+Uniqueの冗長さと子テーブルの外部キーの冗長さを天秤にかけて、前者の方がまし、と判断しているため
ただし、複合外部キーもメリットがあるため好みの問題だ
（親テーブルの結合を省略できる場合がある。適切な複合自然キーは、それだけが子テーブルで必要になることが多く、思いの外この状況は発生しやすい）
```

________________________________________
## 1. DB設計
________________________________________
入門向けテーブル設計

```text
1. 無関係、1:n、n:nテーブルを意識すれば大体OK
    - n:nテーブルは、次のようにも呼ばれる：link/junction/bridge/多対多/関連エンティティ
2. 明確な理由がなければ、1:1は不要
3. 第四正規形までやると概ね良い
    - 非正規形　：単純な行と列の表になっていない(1つのセルに複数値がある)
    - 第一正規形：単純な行と列の表に書き直した
    - 第二正規形：候補キーの部分キーで一意に決まる列がいなくなるよう表を分解した（部分関数従属の排除）
    - 第三正規形：非候補キー列から一意に決まる非候補キー列がいなくなるよう表を分解した（推移関数従属の排除）
    - 第四正規形：n:n:nテーブルを作らない（自明ではない多値従属性の排除）
4. とりあえず楽観ロック
5. PKは暗黙にindexも作られる
6. Uniqueは、indexも作られる
7. FKは
    - Oracle   : indexは作られない
    - Mariadb  : indexが作られる
    - Postgres : indexは作られない
```

全体設計

```text
1. Ermaster、a5sqlmk2などerツールを使用する
2. 250sql/s（Mariadbはその半分）以上の性能を求められる場合は、REDOログSSD化を検討する
3. 10000sql/s（Mariadbはその半分）以上の性能を求められる場合は、垂直分割・水平分割を視野に入れる
4. OracleはAL32UTF8、mariadbはutf8mb4（※）、postgresはUTF8
    - ただしそもそも、Oracleで全角ならNvarchar2を使え
5. データのアーカイブ戦略（非rdbへの移動）を定義し、実践検証もする
6. バックアップ&リストア戦略を定義し、実践検証もする
7. フェイルオーバークラスタリングを構築する
8. データベース設計にかかるコストを省略しない

（※）Mysql寿司ビール問題は、Mysqlはコレーションで解決済みだが、mariadbが非対応
```

命名規則

```text
1. 大文字小文字は区別しない（""囲み定義やSQL Server特殊設定にしない）
2. snake_caseとする
3. スキーマ全体で、同じ意味の列は同じ名前にする
    - 適切に正規化されていた場合、この条件を満たすものはほぼ履歴テーブル、外部キー、共通列のみになる
4. テーブル名はプロジェクト全体として単数形にするか複数形にするか統一する
5. テーブル名は接頭辞をつけない
6. ビュー名は、v_接頭辞をつける。また、特に理由がなければn:nテーブルと同じように、1行に特定するための候補キーがビュー名から自明になるようにする
7. PK/自明なfkスタイルはプロジェクト全体として次のどちらの形式にするかを統一する
    - PK = fk = テーブル名の単数形_id
         - ただし、○○_idと有効期間fromによるナチュラル複合キーに対するサロゲートpkの場合は○○_key
             - これに関しては、複雑な設計理論が必要になるため、次項の「有効期間周りの設計」を参照のこと
    - PK = id, fk = テーブル名の単数形_id
8. 自明でないfkは例えば次の通り。user_id_owner、user_id_applicant
9. 接尾辞の活用
    - ○○_key：整数型の○○_idと有効期間fromによるナチュラル複合キーに対するサロゲートpk
    - ○○_id：整数型のpk、自明な外部キー、有効期間fromと共にナチュラル複合キーを構成する部分キー、前述部分キーに対して外部キーライクな関係を持つ列
    - ○○_cd：文字列型の表示上のコード
    - ○○_ymd または ○○_on：文字列型の'yyyyMMdd'
    - ○○_time または ○○_at：日付時刻型
    - lsn：Lock(log) sequence number。楽観ロックチェックバージョン番号である
    - created_at：日付時刻。登録時刻
    - updated_at：日付時刻。更新時刻
    - ○○_kbn：OTLT値
10. n:nテーブルは、テーブル名_テーブル名。言い換えると1行に特定するための非PKの候補キーがテーブル名から自明になるようにする
11. SQLキーワードやプログラムキーワードの使用は避ける
12. delete_flgよりdeletedテーブルのほうがまし。そもそも一先ずなしで考えろ
    - 十中八九、それは非表示か、利用禁止か、退会状態だ。deletedとは別なはずだ
        - 既存データがfkを持ちその元が上記のような操作をされた時、ロードできるべきなはず
    - deletedは、delete文の発行の代わりによってのみ使われるべきだし、それならDeletedテーブルを用意した方がよほどスマートだ
```

有効期間周りの設計

```text
Effectivity  
https://martinfowler.com/eaaDev/Effectivity.html

○○_idと有効期間fromによるナチュラル複合キーに対するサロゲートpkの場合は○○_key

- 他のテーブルはidとkeyのどちらを持つべきか？
- この設計で○○_idを他のテーブルに持たせる場合、主キーでも一意キーでもないため外部キー制約自体はできない
- トランザクションデータが有効期間ありマスタのkey、idどちらと紐づくべきかは、業務要件に対して以下がどうなっているかを明確にする必要がある
    1. その有効期間ありマスタは、現在より過去のマスタを変更できるか？
    2. そのトランザクションデータは、未来の予定を含むか？それとも現在以前の時間と紐づくのか？
        - 過去のマスタが変更できず、トランザクションデータが常に現在以前と紐づく場合、keyと紐づけてもよい。
        - 何らかの予定など、未来と紐づく場合は、keyの外部キーを持つのではなく、ゆるくidと紐づく方が素直である。

※ ○○_idは主キーでもユニークでもないため、明示的にインデックスを張るかマテリアライズドビューを用意する必要がある
```


スキーマ設計

```text
1. NOT NULL制約を積極的に使用する。ただし、本当にNULLが必要ならNULLを許容する
    - 一般に、NULLが多すぎるテーブルは何かがおかしい（ポリモーフィック関連に近いテーブルになっている可能性が高い）
    - しかしながら、適切にDB設計されてもNOT NULLにできない列は発生しうる
2. UNIQUE制約を積極的に使用する。特に、全テーブルサロゲートpk派なら、必ずナチュラルキーにつける
3. 外部キー制約を積極的に使用する
4. 性能問題が明らかでない限り、正規化。冗長化しない
5. 性能問題が明らかでない限り、別の列から算出可能な列を作らない
6. カンマ区切り値は禁止
7. 列であるべき属性を行で持たない（EAVの禁止）
    - OTLTとEAVの関係は後述
8. 行であるべき値を汎用列1～汎用列4といった列持ちにしない（複数列属性の禁止）
9. 複数のテーブルのPKが入る外部キーライクな列を定義してはいけない（ポリモーフィック関連の禁止）
    - 例外：ormの力を借りれるときは、利用してもよい。自作ダメ、絶対
    - n:nテーブルにするか、複数のテーブルに対する基本クラスのような親テーブルでpkを保証させればよい
10. CHECK制約で取りうる値を列挙してはいけない
11. 全文検索エンジンは、専用の機能を利用する。Likeなどに頼ってはいけない
12. 連番はdbの機能を使用する。シーケンス、auto incrementなど。連番にぬけが発生することを理解する
13. 多段Viewは避ける
14. 証跡を残す必要があるかどうかは慎重に検討する（例：消費税率を変えた日付）
15. 場当たり的な集計用キー列は避け、テーブルを1つ追加する（アンチ例：集計用の地方コード）
16. 連番がinteger（21億）を超える心配がないかどうかを考える（DBよりプログラム側に影響）
17. lsn、created_at、created_by、updated_at、updated_byは全テーブルに追加する
18. deleted列を採用するな。必要になった時に、deletedテーブルを採用せよ
    - deleted=0の書き忘れの心配がない。sqlがシンプルになる
    - どうしてもdeleted列を採用したいなら、せめてdeletedが削除状態になる時にはpkを代入するようにせよ
        - ユニーク制約、別レコード再登録、再削除が可能になる

OTLTについて

- 別名：区分マスタ、コードマスタ、列挙型マスタ、app_enums、app_enumerations
    - kbn_id char(5)
    - kbn char(3)
    - val char(2)
    - kbn_nm nvarchar2(100)
    - val_nm nvarchar2(100)
    - kbn_memo nvarchar2(100)
        - kbn_id = CONCAT(kbn, val)。必ず5桁の文字になる
- 本質的にはenum型をExcel集中管理したい、テーブル数を減らしたい、というメンタル面である
    - プログラム上にこの値を利用する条件分岐がある場合、実質的な2重管理であることも忘れてはならない
    - 例：あるプロジェクトに存在したkbn種は194。行数は898
        - 杓子定規に全てテーブル化すると194のテーブル。さすがにcsv、json、xml、otltのどれかにするだろう
- OTLTが持つ懸念点は、constsファイルに近いものである
    - バカでかいconstsファイルにしたくないが、(横断的に使用されてしまうので)const定義は1か所にまとめたい
    - ファイルの場合、constsフォルダ下に個々のenumファイルを保持すればよい
    - アクセスも集中管理する関数あるいはオブジェクトを仲介すればよい
- fc_get_kbnval(kbn_id)を定義しておけば、結合不要
- OTLTにしてよいのは、いわゆるプログラムコード側ではenumに相当する値である（性別など）
    - 間違っても顧客マスタなど時間と共に増えるデータをOTLT内に入れてはならない
    - 列挙値以外の意味を持つ、例えば名前と(外部連携や画面に表示する必要がある)コード値の両方の列が必要になる場合も対象外
        - 例えば都道府県コードはこれに該当する場合がある。その場合は独立したテーブルを作るべき
        - 実は性別に関しても、ISO 5218に合わせよう、連携時にはISO 5218のコード値で渡そうなどが考えられるなら独立したテーブルにするべき
        - 飽くまでvalは区分内の連番であり、それ自体に連番以上の意味を持たせてはならない
        - 言い換えると、intやstringにキャストする前提があるenumはOTLTにしてはダメ
    - 100値以上の列挙値も対象外
    - コード値で検索したい、など名称以外に意味を持たせたい場合も要調整
- app_enumsよりkbn_mstがおすすめ
    - enumはDBや言語で予約語であることが多く、名前に使いにくく、結果app_接頭辞をつける羽目になる
    - enumで文字列検索すると予約語がヒットしてしまい、扱いにくい
    - app_enumsにすると、各列のあるべき名前がapp_enum_idなどになり長い

OTLTに関しては、下記も一読すること
https://www.slideshare.net/suno88/otlt-86775847

設定マスタについて

- 別名：定数マスタ、configs、app_configs、app_configurations
    - config_id number
    - config_nm nvarchar2(100)
    - config_val nvarchar2(1000)
- 基本的に功罪は区分マスタと同様
```

________________________________________
## 2. SQL設計とIndex設計
________________________________________
SQL規約

```text
1. 厳密な計算が必要な時、SQL上でROUNDを使わない
2. Mariadbやsqliteで、group byではない列をselectしない
3. SQL内だけで乱数を生成しようとしない
4. あまりにも複雑なSQLを1つのSQLにしない
5. 日付データ型を使用する場合、デフォルト設定を使用しないで明確化する
    - 日付をリテラルで投入する際は、必ずキャストする
        - oracle : TO_DATE('2020/01/14 10:56:24', 'SYYYY/MM/DD HH24:MI:SS', 'NLS_DATE_LANGUAGE = JAPANESE')
        - mariadb : 不明
        - postgres : 不明
6. YYYYMMDDをBETWEENする場合は、YYYYMMDD文字列にしてから行う
7. 時分秒込みでBETWEENする場合は、適切に時分秒を考慮する必要がある
```

SELECT文の内部動作

Indexes and Index-Organized Tables  
https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721

How to predict Index blevel and when to rebuild  
http://sai-oracle.blogspot.com/2006/03/how-to-predict-index-blevel-and-when.html

```text
結合方式
- ネステッドループ : 外部表（駆動表）、結合される側でループ。内部表（Leaf側）
                     ※ Oracleの内部結合の場合、駆動表は最適なものが自動算出される
- ソート&マージ    : 双方を結合条件列でソートしてマージ
- ハッシュ         : 少行表からハッシュテーブル生成、多行表で突き合わせ

B+Tree索引
- B木（平衡n分木）を作る
    - Oracleの場合
        - B*木
        - 各ノードサイズはブロックサイズ(デフォルトは8KB)
        - Indexキーのデータ長は列のデータ型長に対応
        - リーフブロックはキーとRowIDのペア
        - 均等に分布した場合、概ね以下の通り
            - blevel 0 : ～540
            - blevel 1 : ～540^2 (30万行)
            - blevel 2 : ～540^3 (1.5億行)
- 探索も更新もO(log n)になる
- Indexが機能しない条件は、「INDEX　が　効かない例」を参照のこと
- B-木 : シンプルなB木
- B+木 : フルスキャンのためにリーフ間ポインタ付き。リーフノードのみがデータを持つ。充填率1/2
- B*木 : フルスキャンのためにリーフ間ポインタ付き。リーフノードのみがデータを持つ。充填率2/3

ソートが暗黙にされる条件
- ORDER BY
- UNION ALL以外の集合演算
- 集計関数
- 窓関数、OLAP関数
```

Index設計の目安

```text
1. 1万行以上のテーブル
2. カーディナリティ絞り込み率5%以上
    - 等分布なら20種類以上
    - 集計済Flgで95%以上が1、5%以下の0を取得したい場合、など
3. 結合やWHEREに使用される列
```

INDEX　が　効かない例

```text
1. 索引列に演算を行なっている
2. 索引列にIS NULL 述語を使っている（※）
3. 索引列に対してSQL関数を適用している
4. 索引列に不一致（<>）を用いている
5. 索引列にORを用いている（IN句ならOK）
6. 索引列に後方一致、または中間一致のLIKE述語を用いている
7. 索引列が暗黙の型変換を行なっている
8. 複合インデックスの場合に、列の順番を間違えている
    - ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
    - ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100;
    - × SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500;
    - × SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500;
    - × SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10;

（※）MySQL5.6～、Postgres9.0～はIS NULLもIS NOT NULLも効く。Oracleは効かない。SQL Serverは不明
その他に関しても製品によっては効く場合があるが、一般的には避けたほうが無難
```