質問: いつも本当に助かっています。 テーブルの設計についてご意見をお聞かせください。 仕事でデータロガーのシステムというものを実装しました。 そのシステムは外部の機械から何らかのデータを受けとり、保存していくだけの単純なものですが、受け取るデータ構造を可変としています。 --- 例: * 機械Aは 気温、湿度、気圧 * 機械Bは 電圧、電流 * これらのデータが各機械から10秒毎に(23°C, 30%, 1000hPa というように)送信されるので受信し保存する。 条件: * 機械毎のデータ定義を画面から設定する。 (何カラムあり、どのカラムが何のデータで、型は何で、有効桁数が何桁で、単位は何か、等) * 各カラムのデータ型は真偽値、整数値、小数値、文字列がある。 * 機械Aも機械Bも数千台程度接続されている。今後機械Cも追加される * データを元に各機械毎のグラフを描画したり全体で集計したりする。閾値判定も行う。 * データ蓄積後、定義の変更はある程度許容する。(列の追加や単位の変更など) --- これらを実現するためのテーブル構造の案として、下段に記載した幾つかの案などがあるかと思います。 実はもうこのシステムは「案1」で開発が終了し、問題なく稼働しています。 上記は例ですが、実際に数億レコードのデータが蓄積されています。 ただ、設計実装が私一人で周りに頼れる人もなく、設計に不安があるため、皆様のご意見をお聞かせください。 **質問1.情報が少なくて恐縮ですが、「案1」を採用したのは正解だと思いますか? 質問2.現実的な案として下段以外のものはありますか? 質問3.下段の案の中で、上記条件に対して「ありえない」と考えるものはありますか? 質問4.一般的にこういったもののデザインパターンはありますか? 質問5.他、経験談や参考情報などありましたらご教示ください。** 実際に問題なく稼働しているのだからいいじゃん、という部分もあるかと思いますが、後学のためよろしくお願いいたします。 ※できるだけ広くご意見を頂きたいので少し長めに質問をオープンしておくかもしれません。 CentOS7.2, MariaDB10.0, Java8 --- ### 案 **1.ID, 機械ID, キー(VARCHAR), 値(VARCHAR), 日時 のカラムを持つテーブルを用意する。** ※一旦値をVARCHARで保存し(小数値1.23ならそのままテキストで)、読み出し時に各定義の型に合わせて解釈します。 ※1カラム(例えば気温)だけで1レコード消費します。 ※閾値判定時、VARCHARカラムに対して平気で 「値 > 1.23」 のようなことをやります。 ※1回の送信データを示す集計基準は機械IDと日時です。 ※この案を採用しました。 **2.「定義ごとに柔軟にテーブルを作る」システムを作る。** ※プログラムが CREATE TABLE 文を発行します。 ※なんだか気持ち悪いという理由と、システムが複雑化しそうという理由で却下しました。 ※データ量は最も少なくなり運用も楽そうなため、今はこちらの方がよかったかも、と思っています。 **3.ID, 機械ID, c1, c2, c3, c4..., 日時 のようにカラムを予めたくさん用意し、カラムとデータの対応テーブルを別途用意する。** ※ダサく感じたので却下しました。なしではなかったなと思っています。 **4.ID, 機械ID, データ(VARCHAR), 日時のカラムを用意し、データにXML, JSON, ハッシュオブジェクトシリアライズなどで保存する。** ※集計と運用が厳しくなるかと思い却下しました。 **5.RDBを用いない。** ※RDB以外を知らないことと、集計が厳しくなると思い、却下しました。
回答: 回答が長くなりますことをご了承下さい。。。 始めに === 質問者様の現在の設計は、 maisumakunさんの回答の通り、 **「EAV」アンチパターンとされる設計技法**とされています。 DB設計などについては「**SQLアンチパターン**」という書籍にて、 その**アンチパターンに陥りやすい背景**、 及び**どのように改善すべきか**までまとめられているため、 当方としては是非ご一読してほしい書籍の一つです。 要件に対する設計是非について ============== 「EAV」アンチパターンとはされていますが、 今回の要件のような場合では採用されるケースはしばしば見受けられます。 以下のような懸念が払拭できない場合は、 個人的には**今回の設計方針が一番妥当**とは考えます。 1. 機械情報(機械A、機械Bなど)の登録数は今後も増加していく 2. 機械情報に付随するパラメタ値は増減する恐れが高い 3. 機械情報に付随するパラメタのデータ型も今後増減する恐れがある(論理値、実数、文字以外が加わるなど) 上記の課題がある場合は、 テーブル数の上限の固定が困難なため、 今回のような汎用的テーブルの導入を推す根拠足るとは思えます。 ただしパラメタ値の検索条件の書き方次第で、 データ型のキャストが必要以上に発生することと、 データ集計などには向かないところが玉に瑕とかもしれません。 案2~案5への見解 ========= **案2**について。 「機械ごとに定義情報を作っちゃえ!」ってことであれば、 あまりオススメできる手法ではありません。 理由は「要件に対する設計是非」にあげた懸念1番の問題があるためです。 特に**機械情報の登録・修正・削除の頻度が高い**と破綻しやすい手法です。 **案3**について。 情報によっては無駄なカラム情報を管理するだけになるので、 質問者様が言うように活かした設計ではないかなという印象ですね。 **案4**について。 データの実態自体は、XML, JSON, ハッシュオブジェクトシリアライズで保存ということなので、 それに起因して集計・運用時のノウハウは必要となってくる可能性は高いです。 うまいこと扱う自身がない場合は、 避けた方が無難かもしれませんね。 **案5**について。 当方はNoSQL系の経験が皆無のため、 こちらは有用なアドバイスができません・・・。 ちなみに、sm_ymさんの回答にあがっています、 カンマ区切りで1つのカラムに保持する方法は、 sm_ymさん自身もおっしゃっているよう破綻しやすいです。 「**ジェイウォーク**」アンチパターンしても有名なので、 まったくオススメは出来ません。 EAVを用いない設計方針として =============== 「**EAV**」を回避しRDBらしく課題解決に無理に拘るのであれば、 一例としてあげるなら以下のようなデータモデリングとなる気がします。 下記のような設計とすると、 恐らくですが、新規テーブル定義追加リスクは発生しません。 ただし前提条件としては、 ・今後データ型の増加は起こりえない 上記を満たす必要があり、 問題点としては、 ・機械数・パラメタ数がわずかの場合必要以上のテーブル数となる ・単体では意味がなさないテーブルが多いため・別途ビューの作成が要求されるかもしれない を許容する必要は出てきます。 以下は概念データモデル設計のほんの一例を記載します。 `機械マスタ(ID, 機械名, ... , 登録日, 変更日)` `機械別パラメータ(ID, 機械ID, 連番, パラメタ名, データ区分, 修正日)` `機械別文字列パラメータ (ID, パラメタ値)` `機械別数値パラメータ (ID, パラメタ値)` `機械別論理値パラメータ (ID, パラメタ値)` 上記について簡単に説明します。 先ずは機械マスタですが、 こちらは機械共通の属性情報を管理します。 今後、**機械一覧検索などが必要になった際に一覧表示する情報はここで管理**するとよいでしょう。 尚、**主キーはID**の想定です。 次に機械別パラメータですが、 こちらは各種パラメータ詳細テーブルの親テーブル、つまり汎化したものに当たります。 (**※オブジェクト指向の抽象クラス・派生クラスをイメージするとよいかも**) こちらでは**機械別パラメータの一覧だけを管理**します。 尚、**主キーはIDで、機械ID、連番に対して複合一意キーを付与**する想定です。 ポイントは上記テーブルでは、 * パラメタごとのデータ区分を管理する * パラメタの具体値までは管理させない 上記の2点となります。 パラメタごとの修正・変更日を管理したいなら、 このテーブルにカラムを設けると良いでしょう。 最後に各種パラメータ詳細テーブル(機械別文字列パラメータ)ですが、 こちらで**各種パラメタの具体値を管理**します。 この時点でデータ型は適切に管理されているため、 集計、条件比較に無駄なキャストが入り込む隙は与えません。 数値パラメータはnumeric型やnumber型などを利用すると、 整数・少数どちらでも管理可能です。 あとグラフ化なども行うとのことで、 要件次第ですが例えば全機械の電圧のみをグラフ表示するとかがあるなら、 数値パラメタテーブルに更に種別などをもたせて・・・、 など集計の切り口は詳細テーブル内でも与えられるのかなとは思われます。 尚、**主キーはIDとなり親テーブルの機械別パラメータのIDと同一値**が入る想定です。 データ整合性の観点から各種パラメータ詳細テーブル全てのIDには、 **機械別パラメータのIDを参照する外部キー制約を定義**すると尚良いでしょう。 (**※設計段階で親子関係を明示する意図でも有用です**) ちなみに万一パラメータで保管すべきデータ型が増えたら、 * 各種パラメータ詳細テーブルの追加 * データ区分の追加 上記作業が必要となってきます。 蛇足 == 上記で紹介したテーブル構成だと、 検索時に何かと不便なので、 上記設計のような場合ではビューを作成することをオススメはします。 ついでなので以下にビューのイメージを記載しておきます。 注意点としてはビューで値を簡易的に一覧する場合は、 データ型を合わせる必要があることです。 (※要するにキャストが必要になるのが面倒です^^;) 実際に下記クエリは流してないので、エラーになる可能性大です。 あくまでイメージとして参考にして下さい。 ``` CREATE VIEW "機械別パラメータ一覧ビュー" AS SELECT m.ID AS "機械ID" , m."機械名" AS "機械名" , p."パラメタ名" AS "パラメタ名" , p."データ区分" AS "データ区分" , CASE p."データ区分" WHEN '1' THEN "文字列" WHEN '2' THEN "数値" WHEN '3' THEN "論理値" ELSE '‐' END AS "データ区分名称" , CASE p."データ区分" WHEN '1' THEN p_char."パラメタ値" WHEN '2' THEN CAST(p_num."パラメタ値" AS varchar) /* 下記の論理値は普通のキャストでTRUE、FALSEの文字列となるか怪しいので、 多分直す必要があります。。。 */ WHEN '3' THEN CAST(p_bln."パラメタ値" AS varchar) ELSE '' END AS "パラメタ値" , p."修正日" FROM "機械マスタ" m /* 以下テーブル群はパラメタ未登録の機械も表示要なら結合方法は「LEFT JOIN」へ */ INNER JOIN "機械別パラメータ" p ON m."ID" = p."機械ID" INNER JOIN "機械別文字列パラメータ" p_char ON p.ID = p_char.ID AND p."データ区分" = '1' INNER JOIN "機械別数値パラメータ" p_num ON p.ID = p_num.ID AND p."データ区分" = '2' INNER JOIN "機械別論理値パラメータ" p_bln ON p.ID = p_bln.ID AND p."データ区分" = '3' ``` また、上記だと結局パラメタ値は全て文字となってしまうので、 数値パラメタのみだけに特化したビューなどを検討するのもありかもしれません。 追記 == ただし今回のケースだと上記のようなRDBのルールに厳密に則って、 手間をかけて設計するメリットが薄い気がするので、 「**EAV**」の設計パターンの対策として上記のような手法がある、 程度に抑えておくだけで良いかと思います。