DB設計に関する覚書
テーブルの種類は4つ（リソース、イベント、マスタ、サマリ）
- リソースはモノやヒト（商品、ユーザーなど）
- イベントはコト（購入、ユーザーアクションなど）
- マスタは半固定項目（商品コード、ユーザー区分など）
- サマリはイベントを集計/加工したデータ
正規化は必ず行う（正規化崩しはしない）
- データの整合性を確保することが最重要
- パフォーマンスの為に正規化を崩したくなったら、読み出し専用のサマリテーブルを作る（データはETLする）
更新（Update）は極力避ける
- 状態の変化は挿入（Insert）で表現する
- つまりイミュータブルデータモデル
- ただし、マスタ系のテーブルなど履歴を残す意味が薄いデータはUpdateしてもよい
アプリケーションが使うテーブルとBIツールで使うテーブルは分ける
- BIツールで使いやすいデータモデルはアプリケーションのデータモデルとは相性が悪い（ことが多い）
- BIツールでは大福帳テーブル的なデータモデルが適しているが、アプリケーションとしては保守性が悪いので避けたい
- 正規化されたデータモデルはアプリケーションとしては整合性を確保しやすく好適であるが、BIツール側から見ると結合が多く発生する為、分析クエリが組みにくい
- したがって、分析用途で使うテーブルはアプリケーションとは切り離された別テーブルとして提供するべきである（データはETLする）
- 規模が大きくなってきたらデータレイクに発展させることも検討するべき
テーブルの説明書を用意する
- テーブルの想定用途、設計意図、注意事項などを列挙したテーブル説明書を作る
- テーブル説明書はGoogle Docsや社内ポータルなどリンク可能な場所に配置する
- ↓のようにテーブルのDDLにコメントとしてリンクを記載しておく
CREATE TABLE my_table ( column1 int, column2 text, ... ) comment='http://example.com/my_table.html'; -- テーブル説明書へのリンク
DBだけで全てを解決しようとしない
- トリガーやストアドファンクションなどを駆使してDBで処理を完結させるような設計は原則として避ける
- DBでロジックが実行される場合、DBサーバは基本的に多重化できない為、マスターとなるDBサーバに負荷が集中してしまう
- トリガーやストアドファンクションはエンジニアからも見えづらく、修正/検討漏れが発生しやすい為、バグの原因になりがちである
- ビジネスロジックやデータ変換処理は極力アプリケーション側で行うようにするべき
オンラインで全てを処理しようとしない
- トランザクションテーブルへデータ保存→サマリデータを作成→サマリーテーブルに保存というようなデータ登録が連鎖するような処理をオンラインで行うことは避ける
- この場合だとトランザクションテーブルへのデータ保存のみオンラインで行い、以降の処理はジョブキューを利用した非同期処理として設計した方がよい
データは極力物理削除する
- 論理削除の利便性は確かに存在するが、レコード数が増加し、削除済みレコードが蓄積されるとクエリのパフォーマンスに影響を及ぼす
サロゲートキーを使う
- 色々言われていることは承知の上だが、まずはサロゲートキーを選択した方が無難
- ナチュラルキー、特に複合キーはアプリケーションの修正に伴い、キー項目が増殖していく可能性が高い
- キー項目が増殖していくと、一意のレコードを取り出すのに途轍もない手間がかかりバグの温床になる事例が多い
NULLは極力排除する
- カラムのデフォルト値がNULLになる項目を作らない
- デフォルト値がNULLになるカラムは本来そのテーブルにあるべきではない可能性が高い
JSON型の利用は最小限に留める
- JSON型を使うと第一正規形すら守っていないテーブルになる
- DBは正規化されているべきなので、正規化の観点からJSON型の利用は避けるべきである
- ただし、以下の場合はJSON型を使った方が効率がよい
- (1) データモデルの変更頻度が激しい
- (2) 半構造化データである
- (3) 基本的に独立したデータである（他のテーブルに強く依存していない）
- 具体的にはアンケートフォームなどが該当する
- アンケートフォームは大抵一度きりの利用やKPIを測るために頻繁に変更されがちである
- そのようなデータの為に正規化されたテーブルを用意するのは非効率である為、JSON型の利用がむしろ望ましい
固定長コードは極力避ける
- 固定長コードとはコードの各桁に意味があるコード体系のこと（電話番号などが代表例）
- 固定長コードは使い続けることで桁数が枯渇することが宿命付けられている為、将来的な拡張性に問題がある
- 特にビジネスやサービスのコアになるシステムが固定長コードを採用している場合、ほぼ全てのサブシステムが固定長コードに依存することになる
- そのような状態でコードの桁数が枯渇した場合、その影響範囲と修正範囲は途轍もない規模になる
- したがって、コードは極力可変長とするか、桁数に意味を持たせないコード設計を選択するべきである