えむてぃのメモ帳

RDBの設計についてメモ

Posted on 6 mins

設計

Table of Contents

理論的なことはある程度知っている前提。
具体的で、実践的な、泥臭い部分が知りたい人向け。

イマイチだった設計と対処法

イケてない設計≒実装時、実装後にちょっと面倒なことになった設計。

郵便番号はひとつにまとめる

フォーマットは最初に決める。ハイフンなしでいいと思う。
デザイン上、ハイフンありの表示が必要な場合は表示時に付与する。 バリデーションせずにバックエンド側でハイフンを取り除く。ハイフンらしき文字ははたくさんあるので数値以外取り除くでもいいかもしれない。

電話番号はひとつにまとめる

フォーマットは最初に決める。こちらもハイフンなしでいいと思う。
バリデーションせずにバックエンド側でハイフンを取り除く。ハイフンらしき文字はたくさんあるので数値以外取り除くでもいいかもしれない。

カナは全角か半角か最初に決めて全て統一する

バリデーションはしない（モバイルだと半角ｶﾅの入力が面倒）
バックエンド側で必ず自動変換するように統一しておくと◯

少数の値を持つカラムは命名規則をあらかじめ決めておく

flag, status, type, kind, category, etc…

flag, typeあたりを基本使用するようにしておくとよさそう。気分で変えない。
flagは0, 1(booleanが使えるならそれで)、type等は1からの連番(別に0からでもいいがルールは揃える)
flagは本当に2値しか持たない場合(Yes or No)のみ使用する。例えば性別は違う。

住所について

住所は分ける。郵便番号、都道府県、市区町村、番地、建物名。
実は同じ郵便番号で市区町村が違うケースがある(〒871-0000等は県すら違う)。YubinBango.js等のライブラリは後ろが切り捨てられているかいずれか一つが採用される。
実は事業所固有に割り当てられた事業所の個別郵便番号 というものがある(100-8xxx番台など)。YubinBango.js等のライブラリはほとんど対応していない。

削除フラグは持たない

データ取得の度に条件が増える。データ件数そのもが増えるため。
基本的に過去データを保持したい場合は履歴テーブルを作成するほうがより良い選択肢。
履歴系テーブルにはリレーションを入れてはいけない。RDB以外のデータベースも考慮に入る。

お互いのキーを持つようなテーブル構成にしない

例えば1:nの商品、商品価格テーブルで適用中の商品価格テーブルを商品テーブルに持たせるなど

実装が始まってしまってからテーブル構成を弄り回さないつもりで設計する

開発する上でどうしても必要にはなるが「なんとなくこっちが良さそう」で軽率にテーブル名を変えたりしない。
特に設計者と実装者が分かれている時は注意が必要。IDEの機能で手間取らずにリファクタできるなら気にしなくてもいい。

NULLの扱いについて

初期値が定まらない場合は大体NULLを許容することになる。
文字列は大抵初期値"“で事足りるはず(Text型は別)。
明示的にNULLが入る場合以外はNOT NULL制約をつける。
NULLは可能な限り避けておきたいがNULLを避けるために数値や日付に適当な値を入れたりしないこと。

ロジックはDBに持たせない

値が変わったらロジックが変わるような場合はDBに持たせる必要はあまりない。
例えば給付種類テーブル(給付id, 給付名)のような種類が変わったらロジックそのものが大きく変わるようなケース。DBとコードの二重管理になるため。

作成ユーザ、作成日、更新ユーザ、更新日

作成ユーザ、作成日、更新ユーザ、更新日は結構適当になるためRDBもしくはFWの機能があればそれを利用するか処理の共通化と役割をあらかじめ明示しておく。

入力値に制限がない文字列は基本的にTEXT型にする

がMySQLの場合は上限が設定されているので注意する。TEXT型を使用したい場合は大抵LONGTEXT型を使用することになる(htmlテキストを保存するとか)
あらかじめ上限を決めてVARCHARにしておいたほうが都合はいい。

ロック、トランザクション処理について

一連の処理で2回Updateが走る場合ロック＆トランザクションを基本にする。 更新順の定義
ロック順の定義、親→子

Check制約について

要件が自明で99%変更がない場合のみ制約を付与して良い。
税率はNG。都道府県は？

リレーションが貼られているときのデータ更新処理

更新時、削除師の処理はリレーションを貼る時点で決める。
On Update, On Deleteは必ず付与する。

きちんと設計されていればNullableデータはSet Null、そうでない場合はCascadeでだいたいいけるはず。

バイナリデータについて

画像やファイルを持たせる場合は基本的にS3などへアップロードしパスを持たせるほうが良いが、将来に渡って極々少数のデータになる場合はBlobとして保存する選択肢もある。ただし手抜きでやらないようにはしたい。

RDB or No SQL?

大体RDBでいい。 カッチリハマるエッジケース（セッションとか）でRDB以外が選択肢に入ってくる。 少なくともRDBの知識がない人が軽率に選択するものでもない。

PostgreSQLよりMySQL のほうが明確に優れてる点

DB設計でやりたいこと

なかなか一から新規開発の機会は少ないが機会があったらやってみたい。

View, Materialized View(PostgreSQLのみ), サマリーテーブル

参照用のViewを作成して実装を容易にしてみたい。

削除フラグ → ユーザ、削除済ユーザテーブル(trigger)

削除フラグを利用する代わりに削除済ユーザテーブルを作成してそちらに移動する設計。
トリガは構造変更に弱いが実運用でどの程度問題が出るか確認したい。

履歴、操作ログ、ログインログなどをRDBでなくBigQueryなどのNoSQLを使用する

数年に渡る運用で増えたデータ量にパフォーマンスが追いつかないケースがあった。
あとからでは結構厳しいので設計段階で対応しておきたい。

一時的に外部キー制約を無視する設定。リストア時等

主な用途は開発、テスト環境構築用。 MySQL, PostgreSQL両方いける？

RDBのConfig確認、整備

MySQLTuner

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0137

SQLのExplainを一覧化して表示する仕組み

管理画面などでコード上で使用しているクエリのパフォーマンスを自動で一覧表示するような仕組みはできないだろうか。