home
TopAboutWorksBlogContact

[SQL] コード値の定義をデータで保持する

公開日：2024-05-31
最終更新日：2024-05-31

SQL

概要

  • SQLのアンチパターンとしてサーティワンフレーバーというものが存在する
  • Enumに相当するコード値はExcelなんかじゃなくてDBのデータとして管理しよう

という話。

はじめに

みなさん、コード値の定義ってどう管理してますか？
私のこれまでの経験上、多くはExcelやGoogleスプレッドシートといった類のもので管理しているのではないでしょうか。

そしてテーブルのとあるカラムに永続化するものをコード値としたい時にカラムの定義はどうしていますか？

  • 0 / 1 / 2 ... のような数値型にしてDBに永続化し、各数値の論理的な意味をExcelで管理
  • MySQL v8系で実装されたENUM型やPostgreSQLの列挙型のように型で縛る

のような方法を取っていることが多いのではないでしょうか。

個人的にはハッキリ言ってどれもイマイチです。

何がイマイチなのか考えよう

先に挙げた2つの例には色々と問題がありました。まずは何がマズいのか見ていきましょう。

数値型での問題点

確かにひと昔前はDBに永続化するコード値は数値型であるのが主流だったような気がします。
それの最たる理由はおそらくデータのサイズをより小さく留めておくことだったのではないでしょうか。
（あとはパフォーマンスの問題も当時はありましたね）

例えば性別を表すコード値を次のように定義したとします。

物理値論理値
0MALE
1FEMALE

ここで永続化するものを物理値に定義されている数値、INT型だとするならばデータのサイズは1バイトで済みます。
逆に論理値に定義されている文字列、VARCHAR型かつ文字コードをUTF-8だとするならば最大12バイトになります。

業務システムなんかだとコード値となるものはそれなりの数になるし、レコード数もシステムによっては膨大になってきます。
DBのディスクを自分たちで交換していたオンプレの頃は、非機能要件定義の段階でシステム構築時のデータボリュームを見積もり、それがX年後にどれくらいになるか試算し、それに見合うストレージ容量を決めてインフラを組んでいたわけです。
おいそれと簡単にはストレージを引き上げるなんてことは出来ませんし、そうなるとデータのサイズにもかなり気を使うことになります。

それがAWSやGCPといったクラウドインフラが主流となった今の時代ではどうでしょう？
ストレージを引き上げることだってそうですし、なんならDBそのものスペックを変更することさえ、いとも簡単に出来てしまう時代になりました。

もちろんデータのサイズにまで気を配ることは完全に忘れていいという話ではありませんし、そういう知識が役に立つ場面は意外にあったりします。
ただ常に意識しないといけない問題かというとそのレベルではなくなったのではないか、というのが私の感覚でもあります。

そうなると私が常に意識しているのは 運用・保守のしやすさ という観点になってきます。

先の例でいうと、データを見る際に「0はMALE、1はFEMALEだ」という変換表を脳内で持ちながら作業をすることになります。
コード値がこの1つだけなら何も問題ないでしょうが、現実はそう甘くはありません。
コード値が5つに増えただけでもしんどくないですか？それにコード値によっては0、1だけではなくより多くのパターンが存在するかもしれません。

それではDBに永続化するのをMALE、FEMALEのように論理値に定義している ヒューマンリーダブルな値 にしたと仮定しましょう。
データを見る際に脳内変換をする必要なんてありません。だってMALEはMALEでしかないし、FEMALEはFEMALEでしかありません。

まとめると 物理的な値と論理的な意味の脳内変換を必要とするものは、運用・保守まで見通すと効率悪いよね というのが数値型での問題点です。

ENUM型・列挙型での問題点

PostgreSQLは割と前からありましたが、MySQLはv8系になって実装された機能である、いわゆる 型で縛る という方法。
意図しないデータが混入することが防げるし、MySQLのENUM型は確か内部的にはデータを数値で持っているためパフォーマンスも申し分ないと。
かくいう私自身、この機能が実装された時は「いいじゃん！これ！」と思い、実際に使って開発をしたことがあります。

ただこれも開発が進んでいく内に気付くんです、 めちゃくちゃ変更するの面倒くさいじゃん と。

例えば先の性別の例を使って person というテーブルを定義しましょう。

CREATE TABLE person (
  name VARCHAR(50),
  birthday DATE,
  gender ENUM('MALE', 'FEMALE') -- 性別をENUM型で定義する
);

ある日、開発を進めていく中で仕様変更が入り、性別にLGBTQを足すことになりました。
するとALTER文を使ってテーブルの構造変更をしないといけません。

ALTER TABLE person MODIFY COLUMN gender ENUM('MALE', 'FEMALE', 'LGBTQ');

ちなみに足すならまだこれだけで済みますが、既に使われている値を別の値に変更するなんて日には発狂することになります。

  • 変更先のENUMを追加するALTER文実行して
  • 変更元のデータのUPDATEかけて
  • 変更元のENUMを削除するALTER文実行して

みたいに鬼のような作業が待っています。こんなのやってられません。
私なら発狂してテーブルごとDROPしかねません。

で、実はこのENUM型のように限定する値を列定義で指定することが サーティワンフレーバー というSQLアンチパターンに該当するんです。

詳しくはこちらの書籍を読んでみてください。おすすめです。

SQLアンチパターン

ほなどうすんねん

コード値を格納するテーブルを定義する

まずはコード値をデータとして格納しておくためのテーブルを作っておきましょう。
なおこのテーブルはコード値ごとにテーブルを作りましょう。

CREATE TABLE gender (
  code VARCHAR(6) NOT NULL,
  label VARCHAR(30) NOT NULL,
  PRIMARY KEY (code)
);

コード値をデータとして管理する

コード値の定義はExcelやGoogleスプレッドシートではなく、データとして、つまりDMLとして管理しましょう。
後述しますがポイントはDMLというテキストベースのファイルであることです。

INSERT INTO gender (code, label) VALUES
  ('MALE', '男性'),
  ('FEMALE', '女性');

紐づけるカラムにFKをはる

先ほどの person テーブルではENUM型でカラムを定義していましたが、シンプルに文字列として定義をした上で、コード値のテーブルにFKをはってやりましょう。

CREATE TABLE person (
  name VARCHAR(50),
  birthday DATE,
  gender VARCHAR(6), -- ENUM型ではなくVARCHAR型で定義
  CONSTRAINT fk_person_gender -- コード値のテーブルにFKをはる
    FOREIGN KEY (gender)
    REFERENCES gender (code)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

こうすると何が嬉しいのか

変更の容易性

先ほどもポイントとして挙げましたが、コード値の定義をDMLとして管理することで追加や変更、削除の際に差分として検知しやすく、非常に分かりやすくなります。

仕様変更でLGBTQを追加したい！となればDMLに差分が出てくるはずで、それはもう見れば一発で「あ、コード値を新しく追加したいんだな」と分かります。

INSERT INTO gender (code, label) VALUES
  ('MALE', '男性'),
-  ('FEMALE', '女性');
+  ('FEMALE', '女性'),
+  ('LGBTQ', 'LGBTQ');

またコード値を使用する側のカラムにFKをはっているのもミソです。
今回の例では ON DELETE SET NULL / ON UPDATE CASCADE としたので、コード値の削除時には既に使用されているデータのカラムはNULLとなり、更新時には参照している元のデータも合わせて更新してくれます。

個人的には更新時はCASCADEとするのが良いですが、削除時はCASCADEにしない方が良いとは思います。
理由は明白で削除時もCASCADEにしてしまうと、コード値の削除を実行した時点で使用している関連データが一緒に吹っ飛んでしまうからです。
なので削除時はもっとカチッとしたいのであれば ON DELETE RESTRICT のようにしてあげるのも1つの手かなと。

さらにこのやり方はSQLの標準的な機能を用いて実装されているので、DBの種類を問わないというのもかなり大きなメリットになります。
例えば初期段階はMySQLで組んだけど、数年後にPostgreSQLに移行するみたいなことがあっても何ら困ることがありません。

自動生成ツールとの組み合わせ

変更の容易性については大体ググるとみんな同じようなことを書いてるんですが、私の場合さらに自動生成ツールとの組み合わせまで考えます。

コード値なんかはバックエンドはもちろんですが、フロントエンドでも同じものを使用したいという場面は多いはずです。
ExcelだとApache POIを使ったり、GoogleスプレッドシートならGASを実装したりしてコード値のソースコードを自動生成することがよくあります。

ただApache POIは使いづらいし重いし、何よりExcelはライセンス持ってないと中見れないし、GASはそこまで凝った実装をするにはやや不向きだったりもします。

これがデータとしてDBにあったらどうでしょう？
バックエンドのソフトウェアスタックとしてDBへの接続、データの読み取り処理は当然のように実装するでしょうから、その辺りの資産は活用できるはずです。
あとはFreeMarker等のテンプレートエンジンを使ってソースコードを自動生成すればいいわけです。

つまり自動生成ツールを組む上でもデータにしておくと取り回しがしやすくなります。

目次

©︎ s-kugel All Rights Reserved.