利用頻度の低いフラグをカラムを追加せずに管理する方法

こんにちは、バトンズのエンジニア山本です。

アプリケーションを開発しているとテーブルにフラグを追加したい要件が出てきます。 フラグはそのレコードが特定条件に当てはまるかどうかを端的に表すのに便利です。

例えばユーザーのメールアドレスが確認できたらTRUEにするカラムがあるとします。 BATONZでもメールアドレスの確認がサイトの利用に必須なので、 多くのユーザーのレコードでTRUEが設定されています。

このようなメジャーな機能のフラグは特に考えることもないのですが、 機能が増えていくと条件に該当するレコードが少なくかつ、あまり参照もしないフラグというのも出てきます。

フラグを追加する場合、専用のカラムをテーブルに追加する方法がオーソドックスですが、 プロダクトや開発チームによっては アーキテクチャやシステムの複雑さ、社内ワークフローなどの事情で カラム追加自体がハードルが高い行為という場合もありえます。

そんな場合だと、あまり使わないフラグにわざわざカラムを追加するのはコストに見合わないと感じるかもしれません。

また、該当するレコードが少なく、デフォルト値が入ることが多いカラムが増えるとSELECTした際に意味のある情報がぱっと入ってこず調査がしづらいなどのデメリットにも繋がります。

アプリケーションの成長とともにカラム数は増える一方なので 利用頻度の低いフラグならカラム追加なしに済ませたいと思うこともあるでしょう。

ということで、専用のカラムを追加しなくてもフラグを持たせる方法をいくつか検討しました。

手元の環境は以下のとおりです。

• Ruby on Rails 6.1.7.4
• PostgreSQL 11.17

KeyValue的なテーブル

対象のレコードのidとフラグを表す文字列を格納するテーブルを使う手があります。 フラグをONにする場合は、対象のユーザーのIDとそのフラグを表す文字列を持つレコードを作成し、 フラグをOFFにする場合はレコードを削除します。

blog_sample=# select * from user_key_value_flags;
 id | user_id |      flag_name
----+---------+---------------------
  1 |       1 | super_administrator
  2 |       1 | user_create
  3 |       2 | user_create
(3 rows)

これならデフォルト値以外が入ることが少ないカラムを作ってもSELECT時に見通しが悪くなることはありません。 片や、

• ユーザーのフラグを確認するのにusersテーブルだけで完結しない（JoinするかExistsなどでフラグテーブルを参照することが必要）
• どんなフラグがあるかが、テーブル定義からは分からない（実際の値を見ないと分からない）

などの点が専用カラムの追加に比べデメリットと言えます。

Railsならポリモーフィックにすれば複数のモデルで共有できます。

ジェイウォーク

ジェイウォークパターンは1対多を表現したい場合に昔から利用されている手法です。

カラム追加はしますが、1つだけなのでデフォルト値以外が入ることの少ないフラグが増えてもテーブル全体が見づらくなることもありません。 KeyValueテーブル方式のデメリットだった、

• ユーザーのフラグを確認するのにusersテーブルだけで完結しない（JoinするかExistsなどでフラグテーブルを参照することが必要）

は解消されていますが、

• どんなフラグがあるかが、テーブル定義からは分からない（実際の値を見ないと分からない）

はジェイウォークパターンでも同様です。

文字列型なので基本的なSQLで扱えてとっつき易いですが、 フラグをOFFにする際一旦値をパースして対象のフラグ値を削除してから構成し直すなど、扱いに少々手間がかかります。 フラグ値ならあまり使う可能性は高くないですが、デリミタにする文字列（半角カンマとか）を値に使えないという制限もあります。

配列型のカラム

PostgreSQLでは配列型のカラムが作れます。 配列型のカラムにフラグ値を持たせる方法もあります。 こちらもジェイウォーク同様追加するカラムは１つで済みますが、 どんなフラグがあるか分かっていないと検索できない点も同様です。

blog_sample=# select id, flag_attributes from users where id = 1;
 id  |                 flag_attributes
-----+--------------------------------------------------
  1  | {super_administrator,user_create}
(1 row)

Railsの場合、配列型カラムの値を自動でパースして配列として読み込んでくれるので ジェイウォークに比べ値の操作が簡単にできます。

配列の中の値をWhere句の条件にしてSQLを書くこともできます。

blog_sample=# select id, flag_attributes from users where 'user_create' = ANY(flag_attributes);
 id |          flag_attributes
----+-----------------------------------
  1 | {super_administrator,user_create}
  2 | {user_create}
  6 | {invite,user_create}
(3 rows)

　しかし、

ティップ: 配列は集合ではありません。特定の配列要素に検索をかけることは多分にデータベース設計が誤っている可能性を示唆しています。配列の要素と見なされるそれぞれの項目を行に持つ別のテーブルを使うことを検討してください。このほうが検索がより簡単になり要素数が大きくなっても拡張性があります。（ https://www.postgresql.jp/document/8.0/html/arrays.html より）

　という記載がPostgreSQLのサイトにもあり、配列型カラムを積極的に検索することは非推奨なようです。 大きなデータセットから配列型カラムに対しての条件だけでデータを絞り込むことはしないほうが良さそうです。

配列型でフラグを持たないものの検索

配列型カラムでフラグ値を持たせる場合、 「xxxのフラグがあるレコード」の検索は↑のSQLでできます。 逆に「xxxのフラグがないレコード」はどうでしょう。

「ある」のwhere句が = ANY(flag_attributes) だったので、 != ANY(flag_attributes) としてしまうかもしれませんが、 ところがギッチョン。

blog_sample=# select id, flag_attributes from users where 'user_create' != ANY(flag_attributes);
 id |          flag_attributes
----+-----------------------------------
  1 | {super_administrator,user_create}
  4 | {invite}
  5 | {super_administrator,invite}
  6 | {invite,user_create}
(4 rows)

user_createのあるレコードも入ってしまっています。 != ANY(flag_attributes) では「user_createだけを持つもの以外」になってしまいます。 「xxxのフラグがないレコード」の取得にはnot を使います。

blog_sample=# select id, flag_attributes from users where not 'user_create' = ANY(flag_attributes);
 id |       flag_attributes
----+------------------------------
  3 | {}
  4 | {invite}
  5 | {super_administrator,invite}
(3 rows)

user_createを持つものは含まれていません。 一見良さそうですが、 実はこのusersテーブルは以下のレコードがあります。

blog_sample=# select id, flag_attributes from users order by id;
 id |          flag_attributes
----+-----------------------------------
  1 | {super_administrator,user_create}
  2 | {user_create}
  3 | {}
  4 | {invite}
  5 | {super_administrator,invite}
  6 | {invite,user_create}
  7 |
  8 |
  9 |
 10 |
(10 rows)

あれ、notを指定した結果にid 7〜10が入ってませんね。そうです、flag_attributesがNULLのものはnotには含まれません。 （配列型に限らずNULLはIS NULLでしか引っかからないので考えてみれば当然ですが）

なので「user_createフラグがないレコード」を検索する場合は以下のようになります。

blog_sample=# select id, flag_attributes from users where not 'user_create' = ANY(flag_attributes) OR flag_attributes IS NULL order by id;
 id |       flag_attributes
----+------------------------------
  3 | {}
  4 | {invite}
  5 | {super_administrator,invite}
  7 |
  8 |
  9 |
 10 |
(7 rows)

期待する結果と合ってますね。 「xxxフラグがないレコード」の検索は頻繁に使いそうですが、その度にこの条件を書くのはミスの原因になりそうですし、 IS NULLの部分を忘れそうです。NOT NULL制約を入れておけば安心ですが、 そうできない場合は、Railsなら application_record.rbに以下のようなメソッドを加えると都度where句を書かなくて済みます。

def self.where_not_any(val)
  where.not("? = ANY (flag_attributes)", val).or(where(flag_attributes: nil))
end

（ NOT 'xxxx' = ANY(flag_attributes) は 'xxxx' != ALL(flag_attributes) でも同じ結果が得られます。）

カラムを追加せずにフラグ値を持たせる方法についてご紹介しました。 利用頻度の低いフラグはこういった方法での管理でも良さそうです。 利用頻度の高いフラグの場合はこれらの方法で実装するとかえって扱いづらくなるので オーソドックスに専用のカラムを追加する方が良いでしょう。