サーティワンフレーバー
限定する値を列定義で指定するアンチパターン。
私はMySQLを普段使用するためMySQLを例にとって説明する。
MySQLでは列に対して格納、更新などをする際に有効な値を限定する際に「enum型」を用いることで値を限定することができる。
値を限定することができるが格納する値によっては後述するアンチパターンに該当する。
使用する目的
上の項目でも記載したが、列に格納する値を限定することが目的である。
列に格納できる値をすると、無効な値が含まれていない事を保証でき、列をシンプルに使える。
例としてBugsテーブル内のstatus列で、バグの状態を
- NEW
- IN PROGRESS
- FIXED
などで指定しているとすると、status列には必ず、このどれかの値が格納されているという事。
データベースが無効なデータ入力を受け付けないのが理想である。
格納された値は何か
Bugsテーブル内のenumで定義されたstatus値を使用しようとした場合以下のようなクエリを発行するだろう。
しかし、取得したデータはそのままだとenum定義等を含む値が帰ってくるためそのままだと使用できない。
SELECT
column_type
FROM
information_schema.columns
WHERE
table_name = 'Bugs'
AND
column_name = 'status';
+-----------------------------------+
| column_type |
+-----------------------------------+
| enum('NEW','IN PROGRESS','FIXED') |
+-----------------------------------+
多くのプロジェクトでは上記のようにSQLから取得したデータを使用可能な形式に変換するよりアプリケーション側で値のリストを用意して使用することが多いのではないだろうか。
新しい制限の追加
Bugsテーブル内のstatus列に新しい状態を追加する場合はどうしたら良いか。
- NEW
- IN PROGRESS
- FIXED
enumの値やcheck制約を追加、削除する構文はないため新たな値の組合せで列を再定義するしかない。
データベース製品によってはテーブルが空でない場合でなければ列定義を変更できないものもある。
もし、稼働中のプロジェクト等で使用されているテーブルの場合はテーブルの内容をダンプし、テーブルを再定義したのちにダンプしたデータを再度リストアする必要になることもある。
列定義の変更は頻繁に行うべきではない。
値の変更
- NEW
- IN PROGRESS
- FIXED
上記の列の値のうちFIXEDを廃止し、CODE COMPLETEとVERIFIEDに分ける場合どうしたら良いか。
新規で登録する場合は新しく定義された値を格納したら良いが、既存のFIXEDが格納されている行をどうするべきか。
NULL？ 初期値を設定する？？ 新しく作成した２つの値？？ そのまま残す？？
使用しているプロジェクトや用途によって取捨選択する必要がある。
移植が困難
check制約やenumなどデータベース製品によって列定義を限定する方法が統一されていない。
先ほどまで何度か紹介したenumはMySQL固有の機能である。
複数のデータベースをサポートする場合やデータベースを別の製品にする場合は製品ごとに差異があるため困難となる場合がある。
アンチパターンを用いても良い場合
値セットが変わらない限り問題にはならない。
以下のような例が当てはまる
- ON / OFF
- IN / OUT
- 右 / 左
- 有効 / 無効
上記のような相互排他的な値に対して指定する場合は問題ない。
解決策
参照テーブルの作成
参照テーブルを作成して許可する値を１行に１つずつ格納する。
そして外部キー制約を宣言し参照テーブルを参照するようにする。
参照テーブルの値の更新
UPDATE文を用いて名称を変更するだけで良い。
また、新しい限定する値を追加する際はINSERTで追加するだけで良い。
廃止された値のサポート
外部キーを設定しているため行の削除はできない。
参照テーブルに廃止を区別する列を追加することで解決できる。
| status | active |
|---|---|
| NEW | active |
| IN PROGRESS | active |
| FIXED | inactive |
| CODE COMPLETE | active |
| VERIFIED | active |
有効な値を検索する際はactive列がactive、無効な場合はinactiveを条件に検索することで解決できる
移植が容易
enum型やcheck制約と違い、参照テーブルを用いた方法は標準的なSQLの機能を用いているため移植が容易である。
値は行に格納するため他の制約とやデータ型違い実質的に無制限に値を格納できる。
まとめ
列に入力する値を限定するときは、値セットが固定されている場合はメタデータを、流動的な場合はデータを用いましょう
参考文献
