グレーな論理設計を見極める

# はじめに

https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6DB%E8%A8%AD%E8%A8%88-%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-%E3%83%9F%E3%83%83%E3%82%AF/dp/4798124702

↑ の第八章を整理してまとめたメモです

# 主キーとして不十分

3つのパターンがあります

1. 入力データに一意となるキーがない
    * そもそも、そんなデータをDBに登録するべきではない
1. 一意ではあるが、サイクリックに使いまわされる
    * 値の枯渇によって起こる
1. 一意だが、途中で指す対象が変わる
    * データ同士の統合などによって変わる

## 解決方法

### 代理キー（サロゲートキー)を使う

しかし、一般的には主キーとして自然キーを使うべきです。
なぜなら、論理的にも業務的には本来は不要なキーのためです。

### 自然キーを使うには？

>一意ではあるが、サイクリックに使いまわされる
一意だが、途中で指す対象が変わる

こちらの2パターンについては、
* タイムスタンプ
* インターバル

の2種類の方法で列を追加することで自然キーを主キーにすることが可能です。

* タイムスタンプ
    * データ形式が単純なので、SQLでの条件もシンプル
    * ただ、レコード数が増えやすい。そのため、頻繁にレコードが変更される場合データが膨大になる
* インターバル
    * タイムスタンプと違って、スナップショット別のデータを持つ必要がなく、有効期限が切れたデータに対しては終了年月を格納するカラムを追加するだけで良い。
    * その結果、レコード数を減らすことが出来る
    * SQLで必ず範囲指定しなければならないので、少し複雑になってしまう

# 列持ちテーブル

## 配列型は使えないが、配列を表現したい

![スクリーンショット 2023-03-28 16.08.55.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/536974/0ca57a57-546a-b8e2-e6ce-750c1c00f4b8.jpeg)

* 利点
    * 設計がシンプル。正規化とか、スカラ値を知らなくても見れば分かる
    * 入出力フォーマットと合わせやすい
        * 単純にSELECTした結果を流せば良い
* 欠点
    * 列の増減が難しい（拡張に乏しい）
    * NULLを使わなければならない
        * NULLが演算に含まれることで結果を混乱させることになる可能性がある

# 行持ちテーブル

![スクリーンショット 2023-03-28 16.18.14.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/536974/0bf4d828-931f-7335-d2b6-06ef0626e794.jpeg)

特殊な状況以外は基本的には欠点の少ない行持ちテーブルを使用するべきです。

そして、列持ちと行持ちの変換は保証されていて、SQLで変換が可能。

**最初は拡張性の高い行持ちテーブルでデータを保存し、
パフォーマンス上どうしても列持ちへ変換したくなった場合にのみ作る**という方針が良いです

# アドホックな（場当たり的な）集計キー

DWH / BI分野の論理設計を行う際に使用される

![スクリーンショット 2023-03-28 16.30.54.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/536974/fd900df9-7d04-6bc1-bdc4-b913e9355461.jpeg)

地方毎の集計を行いたいがために、集計用に地方のコードを追加する

これは、別のコード体系が必要になったり、サイズの大きいテーブルに対してさらにアドホックキーを追加していく事でさらに、テーブルが大きくなる原因となります。

## 解決策

### 1. キーを別のテーブルに作成する

レコード数がトランザクションテーブル（ユーザーによって登録されるテーブル）に比べてかなり小さくなります
また、メンテナンスが容易です。

しかし、結合のする必要があるためパフォーマンス面では劣ります

### 2. ビューを使う

オリジナルのテーブルには手を加えず、それとは別で地方コードを追加したビューを作成します。

実質的にはオリジナルのテーブルにアクセスしているのとコストは変わりません。
そのため、必要なコードの数だけビューを作ってもパフォーマンスは変わりません

### 3. GROUP BYの中でアドホックキーを作る

# 多段ビュー

ビューは、SELECT文を保存して一種のテーブルとして扱うことが出来ます

ビューの短所としては、
*　パフォーマンスに悪影響を与えてしまうこと
*　濫用すると設計と実装に複雑にしてしまいます

## ビューへのアクセスは2段階で行われる

ビューはデータを保持していませんが、利用者からはテーブルと同じように使用することが出来ます
しかし、ビューは結局のところ、**SELECT文を実行してオリジナルのテーブルにアクセスしている**にすぎません。

つまり、ビューにアクセスする際には2段階のアクセスを行なっています

> ユーザー → ビュー → テーブル

そのため、無視して多段のビューを作ることはバッドノウハウとなりえます

# データクレンジングの重要性

これまで業務で使われていたデータをDBへ登録できる状態にすることをデータクレンジングと言います
元々、システム化されていなかったものをシステム化する場合や、RDBを使用していなかったシステムからリプレイスされる場合に移し替えます。

> オリジナルデータ（汚れた状態） → データフォーマットの調査 → データクレンジング → オリジナルのデータ（綺麗なデータ） → DB設計 → DB登録

## 一意キーの特定

元データに一意のキーがない場合、似たようなデータであったとしても同一データかどうか特定することが出来ません。
その場合は、以下の名寄せが効果的です

## 名寄せ

似たような名前を集めて統合することです。
名寄せを行わないまま、複数DBで管理を行うとバッドノウハウである「ダブルマスタ」を生み出すことになります

# 参考

https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6DB%E8%A8%AD%E8%A8%88-%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-%E3%83%9F%E3%83%83%E3%82%AF/dp/4798124702