概要
利用可能端末を保持する際に、子テーブルを用意して、利用可能端末分だけ親テーブルに紐づくレコードを子テーブルに登録すると、検索時の結合コストが上がって嫌だったので、１カラムで管理できるようにしました
利点
- 結合する必要がないので、SQL のコストが下がる
- １テーブルで管理できるので、登録/更新時のトランザクション制御が不要になる
方法
1. 管理したい端末種別分のビットカラムを用意する
ex.) 8種類なら BIT(8) 型のカラム
OR マッパーによっては BIT 型をサポートしていないことがあるので、カラムのバイト数は増えますが SMALLINT 型などを採用するのが良いと思います
2. 各ビットに端末種別を割り振る
各ビットに端末種別を割り振り、そのビットが 1 なら利用可能、0 なら利用不可能として運用する
ex.) 最下位のビットに PC を割り振る場合、00000001 なら PC 利用可能
実装
具体的にどう使用するかを書いていきます
1. テーブル定義
CREATE TABLE A (
-- 省略
device_type SMALLINT NOT NULL
);
端末は次のように割り振るとします
- 00000000 00000001 → PC
- 00000000 00000010 → MOBILE
- 00000000 00000100 → SENYOU
2. 検索
検索条件に使用可能端末を0から複数件指定する場合、次の SQL で検索します
SELECT * FROM A WHERE device_type & $1 > 0
この $1 には検索したい端末種別のビットORを渡します
ex.) PC と MOBILE のどちらかが使用できるものを検索する場合
00000000 00000001 と 00000000 00000010 をビットORした値
00000000 00000011 つまり10進数の 3 を $1 に渡します
もしレコードの device_type の値が 00000000 00000001 なら
device_type & 3 は 00000000 000001 になり
device_type & $1 > 0 が真になります
3. 登録
検索の逆で、使用可能端末のビットORを登録します
ex.) PC と SENYOU を利用可能として登録したい場合
00000000 00000001 | 00000000 00000100 ＝＞ 00000000 00000101
つまり 5 を登録すれば良い
おわりに
一見分かりにくいですが、管理するテーブルが増えず、バイト数も最小限に抑えられ、SQL のコストも下がるので個人的には好きです。
ただ、この方法で管理しているのを見たことがなく、バッドプラクティスなのかもしれないので、詳しい方がいたらご教授ください