SQL

SQLアンチパターン:ENUMは使わない方がいいよ

どうも、シローです。

今回は、特定の文字列に限定した列を定義する方法について、アンチパターン「サーティワンフレーバー」も一緒に紹介します。

特定の値を持ちうる列とは

例えばユーザ(Users)の権限(管理者かメンバーか)を表したい場合、Usersテーブルにメンバーの権限を表すuser_roleという列を定義するとします。

このuser_roleには管理者の場合はadmin、メンバーの場合はmemberという値を持ちそれ以外の値は格納しない(できない)ようにするにはどうすれば良いのか

 

アンチパターン「サーティワンフレーバー」について

先にアンチパターンについて紹介します。

サーティワンフレーバーと呼ばれる設計方法では特定の値を入れるために

  • 列にCHECK制約を入れる(MySQL8.0以降)
  • 列をENUM型にする

という手法を取りうることです。

僕の環境はMySQL5.7なのでENUM型のカラムuser_roleを作成してみます。

adminmemberは追加できるがbananaは定義されていないので追加できません。

僕もよくENUM型のカラムを作成することはありましたが、SQLアンチパターンで書かれた通りの副作用に直面した記憶があります。

ENUMで持ちうる値がDB定義を見ないとわからない

どの値を持ちうるかはDB定義を見なければならず、次のようにDESCを用いて確認できます。

後からENUMの持ちうる値を更新するのが大変

ENUM型の欠点は後から値を修正したり、削除するのが手間かかることです。

ENUMに新しい値を追加

新しい値を追加するのは比較的簡単です、例えばゲストとしてguestを追加してみます。

値を追加。

ENUMの既存の値を更新

やっぱり、guestではなくてcustomerにしたい場合。

エラー起きました。原因は既にguestの値を持つレコードがある状態でENUM値からそれを削除することができないからです。

じゃあどうやって、やるのかというと

  1. 変更先のcustomerをENUMに追加
  2. 既存のguestcustomerに更新
  3. ENUMからguestを削除

とします。

 

つまり、合計3つの処理が必要です。

しかもテーブル定義のSQLはトランザクションで管理されないので、この間に他の処理が入って予期せぬ不具合が生じることも否定できないです。

 

解決策:新たにマスターテーブルを作成し、そこへの参照キーとして列を定義する

解決策としては、ENUMで取りうる値を別のテーブルにレコードとして管理しておき、そのテーブルへの参照キーとして列を定義することです。

このシステムで管理するための特定の値のレコードを格納しているテーブルはマスターテーブルと呼ばれます。

マスターテーブルで取りうる値を定義することで以下のようなメリットがあります。

取りうる値をSELECTで全て取得できる

マスターテーブルに入っているレコードが取りうる値の全てなので、

SELECT文で取得できます。

取りうる値の追加、更新が容易

例えば、新しくownerを追加したい場合はUser_Role_TypesINSERT文で追加すれば良いです。

また、guestcustomerに変更したい場合はUser_Role_TypeUPDATE文を実行するだけです。

参照整合制約を定義する際にON UPDATE CASCADEとしておくことでマスターテーブルの値を更新すると、更新前のレコードも新しい値に書き換わります。

また、クエリも一回で済みますし、元に戻すことも容易です。

まとめ

  • 限定的な値を取りうる列でENUMCHECK制約は後から値の追加や更新が簡単にはいかないことがある
  • 取りうる値をマスターテーブルで管理する手法の方が後から値に追加や更新が簡単
SQLアンチパターン

本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗(アンチパターン)があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。

-SQL

© 2024 Shiro's secret base