DB設計のアンチパターン
完全に新規開発でシステムの設計をしているのだが、 DB設計でちょっと基本的なところでベストプラクティスを共有できていなかったりするので、 DB設計のアンチパターンみたいな感じで役に立つ実践メモを残しておく。
無論、indexを適切につける、程よく普通に正規形にして設計する等といった当然のことは踏まえた上での話。
必ずArtificial Key（いわゆるサロゲートキー）をPKにする。PKは常に1フィールドで完結させる。
これは"id required"というSQLアンチパターンの正反対の主張だが、ORM前提の時代では実質そうせざるを得ない。 そもそも"id required"アンチパターンそのものが誤っている。 中間テーブルの重複問題は複合ユニーク制約やアプリケーション側の処理で対処すべきものである。
長期的にはナチュラルキーはPKにすることがかなり困難。
複合ナチュラルキーでPKを構成するとほぼ確実に後悔する。
複合ユニーク制約は積極的に使って良い。
今までテーブル数40～200程度の規模の業務システムを10以上扱い、そのうちの8つ程DB設計をやってきて、 Artificial KeyをPKにしているDBもそうではないDBもいろいろと関わってきた上での結論（個人レベルで開発している簡単なツールとかも含めると20以上のシステムでの経験からの結論となる）
基本的にはPKはオートインクリメントの整数を振れば良いが、 分散システムとか複数テーブルにまたがるトランザクション等を考えるとUUIDやNanoID等も選択肢に入ってくる。
外部キー制約をつける
「外部キー制約をつけると実際の保守業務で大変なことになる」という話をよく聞くが、 そうならないような設計にした上で必ず外部キー制約をつけておいた方が良い。
「外部キー制約を意識して手動データ修正などができるように保守チームを教育する」コストを気にしているのだろうが、 「外部キー制約によってデータの関連性がRDBMSの機能レベルで追跡できる」という絶大なメリットと比べるまでも無い。 設計資料が無くても外部キー制約からデータの関連が見えるというのは、保守チームからしてもありがたい話のはず。
そもそも外部キー制約がある状態での正しいデータ修正方法などを学べば済む話ではある。
varchar型の文字列長は、インデックスが許す限り長めに取ると良い。
かなり長い文字が入り、全文検索インデックスぐらいしか貼れない場合はText型を使う。
それ以外ではvarcharを使うと思うが、制約代わりに文字列長を指定しないほうが無難である。 例えば電話番号は15桁までと決まっているが、未来永劫変わらない保証は無いので「とりあえず」多めに取って32文字などとすると良い。
インデックスを駆使して高速にスキャンしなければならない場合は32文字から64文字ぐらいにすると良い。 メールアドレス等、高速スキャンが必要かつ長さが250程度必要な場合は256文字でやってしまえば良い。
本来は意味の違うレコードを一つのテーブルに無理やり詰め込まない
例えばイベント会場を管理するような業務システムで、「宴会場AをXXXからYYYの時間帯で借りたのでZZZ円の請求となった」という記録と、 「宴会のためにXXXという商品をYYY点注文したので合計ZZZ円の請求となった」という記録を無理やり一つのテーブルに格納してはいけない。
記録すべき付加的な情報が全く異なる場合は、 この例だと「会場の時間貸しに関わる請求料金を記録するテーブル」と「注文した商品に関連する請求料金を記録するテーブル」に分けた方が良い。
会場、利用時間帯、注文商品、注文点数といったフィールドを持つテーブルを一つ用意して詰め込んでいく方法はやめた方が良い。
ORMを使うことにこだわりすぎない。集計処理等でパフォーマンスを出す必要がある時は生SQLも積極的に使う。
CRUDのReadについての話になるが、 group byや副問合せを多用するような複雑なread（select文の発行）は普通に生SQLでいい。
というか、集計処理系だとデータのフィルタリングから集約の流れまで細かく制御して、 SQLの書き方でパフォーマンスを上げていくことが多いかと思う。 数百万レコードを対象にして複数項目でgroup byして複数項目のsumを出すみたいなやつ。
next: マイベストプラクティス
updated at : 2023-10-28 18:39:29
author : Toshiaki Yokoda