テーブル設計時におけるnull制約の取扱について現時点の結論
はじめに
自社サービスのリニューアルに伴うDB設計に2日間取り組みました。
DB設計は、機能追加に伴う粒度であれば経験がありますが、 サービスの根本から考えるDB設計は始めてです。
自分が知っていること、経験した事を元に理想の構成を考えたり、ググったり、 大手企業での経験もある15年選手の上司に相談しながら設計を深めています。
本記事は、自分が考えたり調べたりしたことの足跡を残すものです。
なお、今後設計が深まるに連れて、記事に記載したことと方針を変えるかもしれません。 そのときには改めて、どのように変更したか、なぜ変更したかをブログに書きたいと思います。
現時点での結論
- 原則
null: false制約を付ける - 全テーブルに付与するtimestamp型の
updated_at,deleted_atはnull: trueとする - integer型の初期値は
0とする - マスタデータの外部キーは、integer型で
signed,null: false,default: -1とする - 定数ファイルに以下2つを追加する
UNKNOWN: -1NOT_APPLICABLE: -2
この結論に至ったプロセスは、以下の通り。
DB設計当初の考え
- 必須項目であれば
null: falseにすべき- FWのバリデーションと規約を一致させるため
- timestamp型の
updated_at,deleted_atはnull: trueにする- 既存の制約でdefaultに
0000-00-00 00:00:00が入っていたが、パット見で値が読みにくかった - 運用する上で
nullか否かが判断できればOKなカラム
- 既存の制約でdefaultに
- 値が存在しない可能性があればdefaultに
nullを入れる
??って、必要です？
冷静に考えると??の後には初期値となる値や文字列を入れるわけです。
$str = $entity->name ?? ''; $val = $entity->price ?? 0;
じゃあnull: falseにしてdefaultに初期値を入れたほうがスムースやん！
ということで、極力null: falseにするよう方向転換。
null撲滅委員会の存在を知る
ググっていくなかでNULL撲滅委員会の存在を知りました。 名前の通り、nullを排除しようという思想です。
記事によると、nullを避けるべき理由に以下の5つを挙げられていました。
- SQL の作成にあたり、人間の直観に反する3値論理を考慮せねばならない。
- IS NULL、IS NOT NULL を指定する場合、インデックスが参照されないためパフォーマンスが悪い。
- 四則演算または SQL 関数の引数に NULL が含まれると「NULL の伝播」が起こる
- SQL の結果を受け取るホスト言語において、NULL の組み込み方が標準化されていない。
- 通常の列の値と違って、NULL は行のどこかに余分なビットを持つことで実装されている。そのため記憶領域を圧迫したり、検索パフォーマンスを悪化させる。
引用元：NULL撲滅委員会
実装時に生SQLで四則演算をする予定はないのですが、
パフォーマンスに悪影響を与えてまでnullを使う理由も特に無いな、と感じました。
自分の脳内でnull: falseが更に優勢になる。
しかしながらupdated_atなどはindexも貼らないし四則演算もしない。
得られる（そうな）メリットと運用時の可読性を天秤にかけたとき、自分の中では後者が優勢。
マスタデータの外部キーについて
以下の記事を参考に、2種類のnullをマスタに登録しUNKNOWNを初期値とすることにしました。
2種類のNULLとは、「未知（Unknown）」と、「適用不能（Not Applicable, Inapplicable）」です。例えば、サングラスをかけた人の眼の色は分かりません。その人が眼の色を持つことは確かですが、サングラスを外して調べるまでは、何色かは未知です。一方、冷蔵庫の眼の色も分かりません。しかしこちらの場合、色が分からないのはそもそも冷蔵庫に「眼の色」という属性を適用不能だからです。
引用元：3値論理とNULL
で、その初期値をいくつにするか。
0と最大値にする0と-1など負の数を使う0と文字列を使う
などといろいろな実例があることを知りましたが、
結果としてUNKNOWNに-1を、N/Aに-2を割り当てることにしました。
また、カラムはsignedのintegerにしました。
UNKNOWNに0を割り当てることも検討しましたが、
現行のシステムでマスタのIDが1以上なものだけではなくbooleanである（⇔0と1の2値である）ものもあったからです。
最大値を使わなかい理由は、レコード一覧を表示する時にレコード数が多いと最大値レコードの存在に気づかない可能性が出るから。 負の数にしておけば、ファーストビューで表示されるはず。
終わりに
現状このように考えています。 まだまだ判断材料が粗いと思うので、日々学びながら判断の精度を高めていきたいと思います。
