0.はじめに
対象者
- 初めてDB設計する人
前提知識
- 基本的なSQL
- PostgreSQL
- 応用情報技術者レベルの知識（正規化）
伝えたいこと
参考図書
目次
- 設計の概要
- 論理設計
- 物理設計
1.設計の概要
用語
- データベース：データの集積
- DBMS(Database Management System)：データベース
代表的なデータベースのモデル
- リレーショナルデータベース(RDB)
- キー・バリュー型ストア
データベースシステムとファイルシステム
- データに制約を付けられる
- データ一貫性保持(トランザクション管理)
そのデータ、本当にDBに格納する必要ありますか？
http://www.oracle.com/technetwork/jp/articles/index-155208-ja.html
データ中心アプローチ(Data Oriented Approach: DOA)
最初にデータがある。プログラムはその次にできる。
昔はプロセス中心アプローチだった。
DBの論理設計と物理設計
論理設計、物理設計の順に行う。
～論理設計で行うことは何かですが、それは現実世界に存在する数多くのデータから、リレーショナルデータベースにおいて、何を、どのようなフォーマットで保存するかを決めることです。
「達人に学ぶ DB設計指南書」P30 より引用。
物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程です。
「達人に学ぶ DB設計指南書」P34 より引用。
論理設計のステップ
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
エンティティは、現実世界に存在するデータの集合体を指す言葉。
たとえば、「顧客」、「社員」、「注文」など。
物理設計のステップ
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長構成決定
- ファイルの物理配置決定
- [感想] インデックス定義をしていない…
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の速度改善
- 関係データモデル