【社内勉強会】DB設計の基礎(2017/06/21)



# 0.はじめに

------------------
## 対象者
* 初めてDB設計する人

### 前提知識
* 基本的なSQL
* PostgreSQL
* 応用情報技術者レベルの知識（正規化）


------------------
## 伝えたいこと



------------------
## 参考図書

[達人に学ぶDB設計 徹底指南書](http://www.shoeisha.co.jp/book/detail/9784798124704)
![DB設計_指南書.png](https://qiita-image-store.s3.amazonaws.com/0/37903/082291e3-1977-63f1-7fc5-023dee1e6499.png)

[SQLアンチパターン](https://www.oreilly.co.jp/books/9784873115894/)
![アンチパターン.gif](https://qiita-image-store.s3.amazonaws.com/0/37903/e57d8409-df8c-d3ec-e0ff-1a2de0ae34ed.gif)


------------------
## 目次
1. 設計の概要
2. 論理設計
3. 物理設計

---------
# 1.設計の概要


---------
## 用語

* データベース：データの集積
* DBMS(Database Management System)：データベース

### 代表的なデータベースのモデル
* リレーショナルデータベース(RDB)
*  キー・バリュー型ストア



---------
## データベースシステムとファイルシステム

http://ossforum.jp/node/708

* データに制約を付けられる
* データ一貫性保持(トランザクション管理)

そのデータ、本当にDBに格納する必要ありますか？
http://www.oracle.com/technetwork/jp/articles/index-155208-ja.html

---------
## データ中心アプローチ(Data Oriented Approach: DOA)

>最初にデータがある。プログラムはその次にできる。

昔はプロセス中心アプローチだった。



---------
## DBの論理設計と物理設計

論理設計、物理設計の順に行う。

>～論理設計で行うことは何かですが、それは現実世界に存在する数多くのデータから、リレーショナルデータベースにおいて、何を、どのようなフォーマットで保存するかを決めることです。

「達人に学ぶ DB設計指南書」P30 より引用。

>物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程です。

「達人に学ぶ DB設計指南書」P34 より引用。



---------
### 論理設計のステップ

1. エンティティの抽出
2. エンティティの定義
3. 正規化
4. ER図の作成

エンティティは、現実世界に存在するデータの集合体を指す言葉。
たとえば、「顧客」、「社員」、「注文」など。

---------
### 物理設計のステップ

1. テーブル定義
2. インデックス定義
3. ハードウェアのサイジング
4. ストレージの冗長構成決定
5. ファイルの物理配置決定


* [感想] インデックス定義をしていない…

----------------
# 2. 論理設計


---------
## 正規化の目的

* 人間が理解できる形で、現実世界の事実を表現する
* 事実の格納方法から冗長性を排除し、データの異常や不整合を防ぐ
* 整合性制約をサポートする

「SQLアンチパターン」P292 より引用

* 原則として、第3正規形までは正規化を行う

※正規化の具体的な手順は、省略します。






-------------------
## 自然キーと代理キー

* 自然キー(natural key): 入力データに最初から存在しているキー
* 代理キー(surrogate key): 自然キーの代理。システム側で決めた連番などの、人工的なキー


主キーが決められない、または不十分なケース（自然キーがない）

* 一意なキーがない
* 一意なキーだが、サイクリックに使いまわされている
* 一意なキーだが、途中で指す対象が変わる

「DB設計指南書」P226 参照

以下、参考サイト
https://amg-solution.jp/blog/8980
http://wa3.i-3-i.info/simple/word1995.html

-------------------
## オートナンバリングの是非

ネット上で議論されている。

>オートナンバーを主キーに使うことは、データモデルを書いている証拠だ。

by ジョー・セルコ


### オートナンバリングの実装方法

* データベースのシーケンスオブジェクト、ID列
    - PostgreSQLならば、sequence, serial型
    - PostgreSQLのserial型はDEFAULT句にnextval(sequence)を設定している


アプリケーション側での実装はダメ！！

* コストがかかる
* 排他制御の仕組みが必要。二人以上が同じ番号を引き当てる可能性あり。


----------------
## アンチパターン EAV(Entiti Attribute Value)

可変属性をサポートする設計。

* SELECT文が冗長で、分かりにくくなる
* 参照制約、NOT NULL制約が付けられない ⇒ 整合性がとれない

----------------
# 物理設計


----------------
## [個人的見解] 制約をできるだけつける

制約の種類

* 主キー制約
* 外部キー制約
* 一意性制約
* CHECK制約
* NOT NULL制約

制約をつける目的

* 格納されているデータの内容を保証
    

制約を付けないと、アプリケーションのソースコードを調べる必要がある

 * 調べ方に漏れはないか？
 * アプリケーションを使わず、直接DBに操作している場合はないか？



[経験] 制約をつけていれば見つけられたバグだったのに…



---------
# 付録


---------
## リレーショナルデータモデルと相性が悪いデータ
* 履歴データ

>リレーションは集合です。よって、各要素同士に順序はありません。しかし、履歴にはどちらかが古いのか、新しいのかという順序が存在します。

「データベース実践入門」P182より引用。

* グラフ理論のグラフ（ツリーもグラフの一種）

---------
## 論理削除の是非

DELETE_FLAG という思考停止フラグ

>「その論理削除した行は、どういう時に参照しますか?」
「論理削除した行が復活する事がありますか?」
「論理削除した行は最終的にどうなりますか?」

http://qiita.com/Jxck_/items/156d0a231c6968f2a474 より引用


* [感想] 私は全テーブルの削除フラグを付けてしまっています…



---------
## そのうち話したいこと

* SQLアンチパターン
    - 単一参照テーブル
    - IDリクワイアド（とりあえずID)
* SQLの速度改善
* 関係データモデル