データーベース設計段階での質問です。
管理しなければならないフラグ項目が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.ビットフラグ(ビットフィールド)のカラムを4つほど作って、検索時ビット演算する
3.項目名(ID)とフラグNo、だけの2カラムで構成した別テーブルを作ってjoinする
4.文字列としてIDを格納し、フルテキストインデックスをはる
などがあると思うのですが、効率のいい設計がいまいちよくわかっていません。
上記の方法以外にも何かいい方法があると思います。
このような場合の、テーブル設計の方法を教えて下さい。
No.4ベストアンサー
- 回答日時:
No.3の方が書いておられるように、テーブルに配列が使えればいいですね。
しかし、これはDBエンジンがユーザーに配列のように見せているだけで、
内部は最大フィールドが用意されていて、使わないところにはnullが入って
いるように思います。
さて、以前の解答で(1)または(3)の方法が良いと書きました。
フラグが立つ確率が20%以上ならば間違いなく(1)でしょう。
確率が10%以下ならば(3)もコンパクトで速いと思います。
さて、集計ですが、(1)も(3)も同じ事です。
(1)では単純に集計できますし、(3)もIDでグループ化すれば
簡単に集計はできます。
丁寧に解説して下さり、ありがとうございます。テーブル設計でやけに悩んだのは今回が初めてです。Saturn5さんの回答内容を見させていただき、より柔軟に対応できるようにしたいと思いました。割合による目安まで含めて下さり、大変参考&勉強になりました。ありがとうございました。
No.3
- 回答日時:
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/funct …
配列型、初めて目にしました。PostgreSQLは使ったことがないのですが少し気になる存在になりました。ただ、SQL99で定められているのにあまり普及してなさそうなのが残念です。DBで標準的に配列型が使えるようになると、今回のようなケースの設計もあれこれ考える手間が減りそうですね。
検討中のDBにPostgreSQLが含まれていないため、今回は少し難しいですが、非常に勉強になりました。ありがとうございました。
No.2
- 回答日時:
検索対象となるテーブルが2次元ということから改善。
仕掛けややこしくしていると思います。1次元テーブルで検索すること考える。
提示してあるテーブルは、1次元テーブルからクロス集計クエリで作成する。
それか、ほかには
縦軸横軸が逆のテーブルも作る、
逆のが別に存在すれば、検索値対象が1項目で100レコードしか存在しない?。
そうですね。2次元なのがおかしいのは重々承知しています。前任者がなぜこのようなテーブル構造にしたのかよくわかりませんが、そのためにテーブルを調整する必要を感じ、質問させていただきました。
逆のテーブルの発想はありませんでした。ありがとうございます。ただ今回のデータの場合、1つのフラグに対して項目が1つだけというわけではありませんので、残念ながら適応できませんでした。別の機会に活用できる知識としていただいておきます。
No.1
- 回答日時:
1.100個のboolean型のカラムを作る
2.ビットフラグ(ビットフィールド)のカラムを4つほど作って、検索時ビット演算する
3.項目名(ID)とフラグNo、だけの2カラムで構成した別テーブルを作ってjoinする
4.文字列としてIDを格納し、フルテキストインデックスをはる
まず、2はダメです。
この仕事を内部でするのがデータベースエンジンであり、これを使うならば1の方法が
いいと思います。
4はデータに無駄が多いと思います。
結論としては1または3でしょう。
1の利点はデータベース構成が比較的単純で、最も検索が速いと思われます。
欠点はフィールド数が多いこと、使わないフィールドが多い、事でしょう。
3の利点はデータベース構成が最も単純であること、フィールドの無駄が無いことでしょう。
また、1レコード当たりのアクセスも最も速いと思われます。
ただし、レコード数がフラグ数だけ発生し、検索の度にグループ化をする必要があり、
Indexが効きにくく、検索に時間がかかると思われます。
正規的でコンパクトなデータベースという点では3でしょうが、最近のHDの高速化、
大容量化という状況の変化、かつメンテナンスのしやすさを考えると1の方法が最善では
ないかと思います。
正規化にとらわれない柔軟な回答、とても参考になります。ありがとうございました。質問時に忘れていた事なのですが、これらのフラグは検索に使うだけでなく、特定の項目にどのフラグが立っているかという集計もすることがあります。もしこれも踏まえて、テーブル構成に関するアドバイスがさらにあればご回答いただけると非常に助かります。
ベストアンサーにさせていただきたいところですが、もう少し回答を募集してからにしたいと思っています。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- MySQL あと、MySQLの文字コードはutf8 気になりますね 1 2022/12/01 07:22
- MySQL `picture` varchar(255) のコマンドで間違いないでしょうか? 1 2022/11/21 04:08
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- Oracle SQL update方法 2 2022/06/22 14:07
- PHP PHPでCSVを出力するさいに、ループの中で前の行の値を変更したい 3 2022/10/27 17:44
- MySQL PHP 画像のアップロード Qiita 2 2022/11/28 04:44
- SQL Server [SQLServer] テーブル名からカラム名を取得する 1 2022/08/23 21:20
- MySQL mysqlの結合について教えてください 1 2022/05/19 15:13
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
カラムの存在チェック
-
マンションのベランダでお酒の...
-
テーブルのSTORAGE句のサイズ設定
-
INDEXの無効化
-
Data Pump で大量データインポ...
-
CLOB型へのINSERT
-
accessでイベントを中止するよ...
-
ACCESS テーブルに2行目から取...
-
Oracleにおいて
-
Oracle8.1iでテーブルご...
-
一括更新の際のロールバックセ...
-
ACCESS 複数テーブル・複数フィ...
-
テスト環境と本番環境で、DBか...
-
統計情報の取得=コミットですか?
-
テーブルのリンク(ACCESS)
-
賢いSQL文がわからない
-
他の処理でselectさせないよう...
-
EXCELで帳票フォームをつくりそ...
-
ユーザーに対しての監査証跡(a...
-
SQLで検索結果の記事を表示したい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
カラムの存在チェック
-
INSERT時に発番を行いたい
-
GROUP BY で列名は指定できない...
-
ACCESS 1クエリでの複数ALTER文
-
カラム情報とはなんですか?
-
データーベース設計段階での質...
-
Viewにインデックスは張れ...
-
データを削除しても表領域の使...
-
異なるスキーマからデータを抽...
-
CLOB型へのINSERT
-
Data Pump で大量データインポ...
-
ACCESS 複数テーブル・複数フィ...
-
postgreSQLのint型は桁数指定が...
-
SQLでスキーマ名(所有者名)の...
-
ORA-00959: 表領域'****'は...
-
datapumpの実行方法について
-
テーブルからのselectにおいて...
-
INDEXの無効化
-
DELETE文でFROM句を省略した場合
-
検索結果の列数を動的に変更したい
おすすめ情報