前提・実現したいこと
MySQL初心者です。初めてDB設計を行います。
MySQLでログデータを管理し、Web上から検索可能なシステムを構築しております。
ログデータには「要素」があり、それぞれのログデータの要素数や要素項目はバラバラです。定期的にデータベースに格納されていきます。
ログデータ全体は50万件程度あります。後に示しますが、ここでいっている50万件はMainTableのidに相当します。
---システム要求と検討方針---
システム要求には、
要素の複数条件検索が可能であること、要素に数値が入ること、要素が追加されること。等があることを鑑みて、
最終的にはEAVアンチパターンのクラステーブル継承を採用致しました。
※典型的なEAVアンチパターンだと思いました。
※「要素の複数条件検索可能」という条件を満たすためには、EAV(KeyValuStore型)ではAND条件が厳しいと考えました。
Key-Value型で(Key-Value)の条件を複数している事例がほとんど見つからなかったです。
---テーブル---
クラステーブル継承したテーブルを下記に示します。mainテーブルはマスタテーブルです。
ログデータはクラステーブルに自動採番(Autoincrement)でデータが納入されていきます。
path_idでMainTableへ紐付けしています。
会社からはアクセスできないので、かなり簡略化と覚えている程度で書いています。
--MainTable--
CREATE TABLE main (
id BIGINT UNSIGNED PRIMARY KEY,
comment varchar(100),
);
--SpeedTable--
CREATE TABLE speeds (
auto_id BIGINT UNSIGNED AUTOINCREMENT, PRIMARY KEY,
path_id BIGINT UNSIGNED PRIMARY KEY,
value INT(11),
FOREIGN KEY (id) REFERENCES main(path_id )
);
--FuelTable--
CREATE TABLE fuels (
auto_id BIGINT UNSIGNED AUTOINCREMENT,PRIMARY KEY,
path_id BIGINT UNSIGNED PRIMARY KEY,
value INT(11),
FOREIGN KEY (id) REFERENCES main(path_id)
);
MainTableの例:
id / comment
1 / 車1
2 / 車2
3 / 車3
4 / 車4
5 / 車5
6 / 車6
7 / 車7
FuelTableの例:
auto_id / path_id / value
1 / 1 / 45
2 / 1 / 50
3 / 1 / 60
4 / 2 / 50
5 / 2 / 70
6 / 3 / 20
7 / 4 / 50
8 / 5 / 30
9 / 5 / 90
実際には上記テーブルのようなクラス継承テーブルが5コぐらいあります。
困っていること
要求にある、「複数条件による検索が可能であること。」を満たすために悩んでいます。
Web設計側から以下の質問を受けています。
例:SpeedTableの値が 20以上 かつ FuelTableの値が 50以上 のMainTableのCommentを取得するには、どのようなクエリが望ましいか。
発生している問題・エラーメッセージ
それぞれのクラステーブルで取得したレコードは必ずしも1つではないため、それぞれのテーブルでGroupByして、結合してHAVINGが使えない。
(条件に50以上などと指定された場合、2つ以上取得する可能性がある)
それぞれのクラステーブルでpath_idを取得できたとして、どのようにMainTableへ結合したらよいか分からない。
試したこと
クラステーブル継承ではなく、シングルテーブル継承を使って全ての組み合わせを1レコードずつ使うことも考えました。
ただ、ログデータを格納する機能側が譲らないため、かなり揉めてしまっています。
また、全ての組み合わせとなると「要素が追加されること」というシステム要求に対しても危険な設計なのかなと思っています。(組み合わせ爆発)
補足情報
MySQL 5.7.23 、 InnoDBを使用しています。
1~2週間程度の初心者です。どうか教えてください。かなり疲弊してしまいました。
システム要求は変えられそうにありません。
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/09/09 10:57
2018/09/09 11:08 編集
2018/09/11 11:49