データーベース設計段階での質問です。

質問

データーベース設計段階での質問です。

管理しなければならないフラグ項目が100近くあり、そのフラグがよく検索対象になります。ただし、1レコードにつけられるフラグは10個までと決まっています。現在使用中のデータベースでは10個のカラムをつくっていて、そこに対象フラグのIDを列挙しています。

例えば
A項目に対して1,20,34,56,78
B項目に対して3,6,11,15,42,78,89
のフラグがたっている場合、

name　f0　　f1　　f2　　f3　　f4　　f5　　f6　　f7　　f8　　f9
------------------------------------------------------
A　　　1 　　20　　34　　56　　78　　null null null null null
B　　　3 　　6 　　11　　15　　42　　78　　89　　null null null

となっています。他に日付などのカラムが5項目ほどあります。
このままだと検索等でややこしいことになるため
テーブルを整理したいと思っています。

1.100個のboolean型のカラムを作る
2.ビットフラグ（ビットフィールド）のカラムを４つほど作って、検索時ビット演算する
3.項目名(ID)とフラグNo、だけの2カラムで構成した別テーブルを作ってjoinする
4.文字列としてIDを格納し、フルテキストインデックスをはる

などがあると思うのですが、効率のいい設計がいまいちよくわかっていません。
上記の方法以外にも何かいい方法があると思います。
このような場合の、テーブル設計の方法を教えて下さい。

回答No.4

Saturn5 · ベストアンサー

Ｎｏ．３の方が書いておられるように、テーブルに配列が使えればいいですね。
しかし、これはＤＢエンジンがユーザーに配列のように見せているだけで、
内部は最大フィールドが用意されていて、使わないところにはｎｕｌｌが入って
いるように思います。

さて、以前の解答で（１）または（３）の方法が良いと書きました。
フラグが立つ確率が２０％以上ならば間違いなく（１）でしょう。
確率が１０％以下ならば（３）もコンパクトで速いと思います。

さて、集計ですが、（１）も（３）も同じ事です。
（１）では単純に集計できますし、（３）もＩＤでグループ化すれば
簡単に集計はできます。

お礼

丁寧に解説して下さり、ありがとうございます。テーブル設計でやけに悩んだのは今回が初めてです。Saturn5さんの回答内容を見させていただき、より柔軟に対応できるようにしたいと思いました。割合による目安まで含めて下さり、大変参考＆勉強になりました。ありがとうございました。

回答No.3

yamada59

PostgreSQL 以外のデータベースで同じことができるか分からないですが、PostgreSQL には配列型というデータ型があり、それを使うと SQL もすっきりと書ける上にインデックスを使って高速に検索できます。

CREATE TABLE t (
name text,
flags integer[]
);

SELECT * FROM test LIMIT 5;
name | flags
------+--------------------------------
1 | {18,99,86,79,35,19,40,80}
2 | {96,14,27,38,80,44,16,99,22}
3 | {53,65,84,85,86,90,94}
4 | {3,13,3,97,77,99,29,25,92}
5 | {77,59,56,97,98,63,34,80}
(5 rows)

CREATE INDEX test_flags_idx ON test USING gin (flags);

フラグに 1 かつ 2 を含むデータを検索する場合には、

SELECT * FROM test WHERE flags @> ARRAY[1,2] LIMIT 5;
name | flags
-------+-------------------------------
90609 | {1,11,39,12,2,32,23,68}
8267 | {86,70,2,33,95,79,94,1,12,15}
15346 | {45,26,81,13,1,14,88,46,15,2}
99014 | {84,58,30,89,97,2,25,95,1}
75943 | {1,1,84,9,64,2,4,9}
(5 rows)

1 または 2 を含むデータを検索する場合には、

SELECT * FROM test WHERE flags && ARRAY[1,2] LIMIT 5;
name | flags
-------+--------------------------------
45020 | {90,1,3,11,33,72,73}
6 | {2,5,27,5,60,81,54,68}
13 | {2,87,33,26,73,22,19,63,73,21}
74259 | {74,26,86,65,22,25,2,15,50}
9987 | {5,42,1,59,86,7,78,82}
(5 rows)

といった感じです。

参考URL：

http://www.postgresql.jp/document/9.0/html/functions-array.html

お礼

配列型、初めて目にしました。PostgreSQLは使ったことがないのですが少し気になる存在になりました。ただ、SQL99で定められているのにあまり普及してなさそうなのが残念です。DBで標準的に配列型が使えるようになると、今回のようなケースの設計もあれこれ考える手間が減りそうですね。

検討中のDBにPostgreSQLが含まれていないため、今回は少し難しいですが、非常に勉強になりました。ありがとうございました。

回答No.2

layy

検索対象となるテーブルが２次元ということから改善。仕掛けややこしくしていると思います。

１次元テーブルで検索すること考える。

提示してあるテーブルは、１次元テーブルからクロス集計クエリで作成する。

それか、ほかには
縦軸横軸が逆のテーブルも作る、
逆のが別に存在すれば、検索値対象が１項目で100レコードしか存在しない？。

お礼

そうですね。2次元なのがおかしいのは重々承知しています。前任者がなぜこのようなテーブル構造にしたのかよくわかりませんが、そのためにテーブルを調整する必要を感じ、質問させていただきました。

逆のテーブルの発想はありませんでした。ありがとうございます。ただ今回のデータの場合、1つのフラグに対して項目が1つだけというわけではありませんので、残念ながら適応できませんでした。別の機会に活用できる知識としていただいておきます。

回答No.1

Saturn5

1.100個のboolean型のカラムを作る
2.ビットフラグ（ビットフィールド）のカラムを４つほど作って、検索時ビット演算する
3.項目名(ID)とフラグNo、だけの2カラムで構成した別テーブルを作ってjoinする
4.文字列としてIDを格納し、フルテキストインデックスをはる

まず、２はダメです。
この仕事を内部でするのがデータベースエンジンであり、これを使うならば１の方法が
いいと思います。
４はデータに無駄が多いと思います。

結論としては１または３でしょう。
１の利点はデータベース構成が比較的単純で、最も検索が速いと思われます。
欠点はフィールド数が多いこと、使わないフィールドが多い、事でしょう。
３の利点はデータベース構成が最も単純であること、フィールドの無駄が無いことでしょう。
また、１レコード当たりのアクセスも最も速いと思われます。
ただし、レコード数がフラグ数だけ発生し、検索の度にグループ化をする必要があり、
Ｉｎｄｅｘが効きにくく、検索に時間がかかると思われます。

正規的でコンパクトなデータベースという点では３でしょうが、最近のＨＤの高速化、
大容量化という状況の変化、かつメンテナンスのしやすさを考えると１の方法が最善では
ないかと思います。

お礼

正規化にとらわれない柔軟な回答、とても参考になります。ありがとうございました。質問時に忘れていた事なのですが、これらのフラグは検索に使うだけでなく、特定の項目にどのフラグが立っているかという集計もすることがあります。もしこれも踏まえて、テーブル構成に関するアドバイスがさらにあればご回答いただけると非常に助かります。

ベストアンサーにさせていただきたいところですが、もう少し回答を募集してからにしたいと思っています。ありがとうございました。