質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.52%

  • データベース

    814questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

  • MariaDB

    360questions

    MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

  • データベース設計

    176questions

    データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

  • IoT

    67questions

    IoT(Internet of Things)とは、インターネットがコンピュータなどの情報・通信機器のネットワークだけでなく、世の中のある様々なモノに接続されて自動認識・自動制御・遠隔計測などの能力を備えることです。「モノのインターネット」と一般的にいわれます。

  • データ構造

    59questions

    データ構造とは、データの集まりをコンピュータの中で効果的に扱うために、一定の形式に系統立てて格納する形式を指します。(配列/連想配列/木構造など)

可変データ構造をRDBに保存する方法について

解決済

回答 16

投稿 編集

  • 評価
  • クリップ 19
  • VIEW 9,781

mosa

score 202

いつも本当に助かっています。
テーブルの設計についてご意見をお聞かせください。

仕事でデータロガーのシステムというものを実装しました。
そのシステムは外部の機械から何らかのデータを受けとり、保存していくだけの単純なものですが、受け取るデータ構造を可変としています。


例:

  • 機械Aは 気温、湿度、気圧
  • 機械Bは 電圧、電流
  • これらのデータが各機械から10秒毎に(23℃, 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以外を知らないことと、集計が厳しくなると思い、却下しました。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 16

checkベストアンサー

+13

回答が長くなりますことをご了承下さい。。。

始めに

質問者様の現在の設計は、
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」の設計パターンの対策として上記のような手法がある、
程度に抑えておくだけで良いかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/20 09:55

    詳細にありがとうございます。

    > 当方としては是非ご一読してほしい書籍の一つです。
    オライリーですよね?買います。

    > 「EAV」の設計パターンの対策として上記のような手法がある、 程度に抑えておくだけで良いかと思います。
    今回の質問の趣旨をご理解いただきありがとうございます。今回のシステムにはおそらく手をいれることはないのですが、この設計の問題や課題、その対策や手法について広くご意見をいただくことが目的でしたので、理解しやすいよう詳細に記載いただいてありがたいです。

    自分のやっている設計が「EAV」である、という認識すらない状態でした。
    後付にはなりますが、設計書に「EAV」(アンチ)パターンで設計されている旨を記載しようと思います。(アンチとは書かないと思いますがw)

    キャンセル

  • 2017/02/27 12:35

    > mosaさん
    ベストアンサーに指名していただきありがとうございます。

    当該質問では、
    様々な切り口からの回答を拝見することができ、
    当方としても大変有益なものとなりました。

    今回は「EAV」パターンに対する、RDBらしい王道の解を提示させていただきましたが、
    様々な要件・制約が出てくるとかのやり方がベストにならないことも容易に考えられるので、
    その辺りのバランスの見極め、そして適切な技術選択をする能力・知見の広さが当方としても課題かもしれません^^;

    キャンセル

+4

現在行われていらっしゃる手法1は、「Entity Attribute Value」という名前が付く程度にはよく行われる(けれども、RDBMSということを考えればそこまで筋が良いとは言い難い)手法です(Qiita)。

とはいえ、「RDBMSだけを使って」「一定の柔軟性を持たせる」場合には必要悪として採用されることもあります(WordPressにもこのような構造のテーブルがあります)。

データセットが数種類しかないのであれば、案2のように「機械ごとにテーブル作成」という手段も実用になるかなとは思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/16 17:13

    ありがとうございます。
    名前あったんですね。私も市販パッケージシステムの中を見たらこういう構造のテーブルがあって、複雑なことやっているな・・・、と思ったことがあります。
    Qiitaの情報ありがとうございます。時間をかけて読んでみます。

    キャンセル

+4

解決済となっていますがIoTシステム体験談と個人的な見解?でも
相談内容と被るところもありますが。

 1.どういう仕様になるのか運用して決定する仕様

データの内容はセンサー(IoTデバイス)に依存するのでいきなりは変わりません。
はじめにA,B,Cのデータを1分ごとに取得するセンサーにしていたが運用してBデータがいらずにDデータが必要になったりします。

データ取得頻度も1分から30秒など変わっても普通のこと

 1.1.仕様がきまらないのはなぜ?

  • だれも経験したことがない
  • 前例がない
  • 参考データがない
  • どういうデータが取れるかわからない

なので、そういうものだと諦める。

 2.最悪を回避するために

  • 生データ(センサー)からのデータを素直に保存

何かしらの変更があった場合、センサー側とシステム側で同期が取れずにデータの取りこぼしや、テーブルの汚染の危険が発生する場合がある。素直?というかバカ正直にデータをそのまま保持するテーブルを持っている。
恩恵に有りつけるのは何かしら問題が発生したときなので、普通の感覚では無駄なテーブルだろと思うのが普通

 3.正規化が危険?

RDBの基礎中の基礎である正規化。これは下手に使用しないことにしている。センサー情報は一度取得したら二度と書き換えることがない(偽造しない限り)から。

重複データは無用な産物だから正規化しようと言うのが普通。だがデータ数が多すぎるとPK、index、whereの書き方をどんなに頑張っても時間がかかる場合がある。それに正規化して単位などを他テーブルから取りに行こうとしたら、ただのSELECTが応答せず、バッチ処理かこれ?と勘違いしそうになったりする

怖いところが、これを体験するのは膨大なデータが溜まったときというところ。安定してるな~と思ってたら最近重くて使えないんだけどと言う話が出て来る

例えば1000センサー10秒毎なら1年間に52560000行になる。
他にも、センサー数が変わったら?10秒が5秒になったら?運用期間が数年になったらなど要因はいろいろ

数の暴力は怖い。

 4.無知なテーブル設計

無知な設計だと思うがsensorA2016テーブルsensorA2017テーブルなど年ごとにバッサリ分けたことがある。
当たり前だが効果は抜群だった。月まで分割してしまおうかと思うぐらいに
ただし、これは年またがりの処理がないからできる

 5.かならず壊れる

物は必ず壊れることを念頭に置く。
センサーのID(シリアルNo)をPKとした場合(複合キー有り)
ID:1000番のセンサーが壊れたからID:2000番のセンサーに付け替えることもある。
システム側としては同じIDのセンサーを付けてくれと言いたくなるが、それじゃシリアルNoじゃないではないかと考えたりする。事実センサーIDの変更=ファームの書き換えが行われる場合が多いので同じIDを用意することは難しい

テーブルのセンサーIDを1000番を2000番にupdateするか、1000番のテーブルをコピー+2000番に書き換えるなども考慮が必要。

一番厄介なのが1000番が壊れたから一時的に1500番に、そして2000番を付けたとかの事後報告。
さらに1500番が他で使われてたら、時系列の情報がなかったらどうしようもなかったりする。

 6.かならず無くなる

センサーのデータが来ないと思ってたらセンサーがなくなっている場合がある。
新しい社員が取ったり電源を切ったり、窃盗?があったり。

 まとめ

設計・仕様書通り作業する仕事がしたい。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/27 11:02

    ありがとうございます。
    将来発生するであろう問題に対しての切り口で大変参考になりました。
    こちらのシステムでは今のところ問題は発生していないのですが、データの削除を行っていないため、将来データがあふれることは確定しています。またインデックスの肥大化も避けられない状態です。
    データ蓄積の目的にもよりますが、基本的には古いデータを削除していく方向でいきたいとは思っているのですが、なかなか決断が下されることがなく。。。

    > 設計・仕様書通り作業する仕事がしたい。
    同感です。

    キャンセル

  • 2017/02/27 13:54

    IoTと一括りにするのは簡単ですが、case by caseの力が多いのでITの一般常識に囚われては行けない時がきます。
    私の経験が活かせるIoTシステムもあるでしょうし、経験があるからこそ深入りしすぎて無駄な設計・製造をやってしまうこともあるでしょう。
    最近でも変更に変更が重なり4割ぐらいがデットロジックのシステムを作ったりしてしまいます。

    「やってみないと分からない」と投げやりな言い方になりますが、これが真実だと思います。

    お互い頑張りましょう><

    キャンセル

+3

案1は割と良く目にします。
要件次第ではありますが、悪手ということもないと思います。
最善手かといわれれば、何とも言えないのですが。

案3もたまに見かけますが、RDBでやるのはどうかなぁと感じます。
レガシーな環境から移行したシステムでありがちっぽいです。

とあるシステムを案4で実装したことがあります。
JSONでデータを格納しました。
ただ、そのシステムはJSON部分で検索や集計をすることがほぼない前提だったため、案4を採用したのでした。
最近はRDBMSにもかかわらずJSONに対して検索できるものもある(新しめのSQL Serverはできたはず)ようなので、そういうRDBMSでしたら選択肢として案4もアリかもしれません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/16 19:12

    ありがとうございます。
    調べてみたら、PostgresSQLやSQL ServerにJSON型というものがあるんですね。
    知りませんでした。
    この手の話、意外とあるということにびっくりしました。
    実績は参考になります。

    キャンセル

+3

私なら多分XMLを使うと思います。
以前アンケートシステムを作ったとき、
レコード単位に構造が全く異なるデータを格納するのに
この手法を利用しました。

RDBMSからみるとデータは共通のキー項目を除くと
単一の文字列という設計。JSONを使う案と基本的に
同じです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/23 11:45

    ありがとうございます。
    要件としてもこの「スキーマレス」というのでしょうか、こういったものが結構あるのですね。
    また、色々な場面でこういった要求があることもあることもわかったため、今回質問させていただいてよかったと思います。

    キャンセル

+2

お世話になります。あまり経験はありませんが、私も後学のため回答させていただきます。

まず、案に関する私の経験の度合いです。
案1 : 設計、実装、保守経験有
案2 : 未経験
案3 : 保守経験有
案4 : 未経験
案5 : NoSql等の運用経験はないのですが、よほどでない限りRDBでできることはRDBでする派です。
※ 案2に関しては、動的に一意となるテーブルを生成するということでしょうか。そういった経験はありませんが、とても興味深いです。

質問1.情報が少なくて恐縮ですが、「案1」を採用したのは正解だと思いますか? 

私も同じ手法ですると思います。トランザクションデータを管理する上ではよく見ます。

質問2.現実的な案として下段以外のものはありますか? 

1カラムにデータをカンマ等の区切り記号連結させ、文字列として格納しているテーブルも考えられると思います。(解析経験ありです。)

質問3.下段の案の中で、上記条件に対して「ありえない」と考えるものはありますか? 

経験したことを踏まえると、案3でしょうか。予備カラムが無駄に思えて。。。

質問4.一般的にこういったもののデザインパターンはありますか? 

質問1の回答と重複しますが、よく見かけます。

質問5.他、経験談や参考情報などありましたらご教示ください。 

質問2の回答の補足ですが、テーブルを解析した際、ドキュメントありき・レコードによってカンマ数が違う等、とても難解でした。。。
なので挙げてはいますが、よほどでない限り採用はしません。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/16 18:06

    ありがとうございます。非常に参考になります。

    > 案2に関しては、動的に一意となるテーブルを生成するということでしょうか。
    はい、そうです。定義に合わせてプログラムが以下のようなテーブルを動的に作り、それに合わせてそれぞれの機械が対象のテーブルにデータを放り込んでいく、というイメージなのですが、実際にこういったシステムを私は見たことはありません。
    CREATE TABLE `機械A` (`id` INT, `機械ID` INT, `気温` DOUBLE, `湿度` DOUBLE, `気圧` DOUBLE, `日時` DATETIME);
    CREATE TABLE `機械B` (`id` INT, `機械ID` INT, `電圧` DOUBLE, `電流` DOUBLE, `日時` DATETIME);

    > テーブルを解析した際、ドキュメントありき・レコードによってカンマ数が違う等、とても難解でした。
    運用保守だけをする側としては本当に大変ですね。いつか自分の設計も後任の担当が同じ目にあってしまうんじゃないか・・・、という不安からご質問させていただきました。

    なので実績があるというお話を聞くとだいぶホッとしますw

    キャンセル

+2

データ量や、頻度、どのような集計・分析を行うかも考慮する必要があるかと思います。

個人的には、ログであること、それなりの量や頻度が見込まれることから、NoSQLを検討すると思います。
NoSQLでも、KVSタイプやドキュメント(JSONがそのまま入ったり)などがあり、集計ができないわけではありません。

http://www.slideshare.net/recruitcojp/rdbnosqlnosql

保守の観点でも、バリデーションができない点で1よりは4ですが、サイズが膨大になることを踏まえると、5になるかなといったところです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/17 09:22

    ありがとうございます。
    これを期にNoSQLを少し調べてみます。
    参考URLもありがとうございます。

    キャンセル

+2

案1と案3のいいとこ取りみたいな、
値の保持をvarcharだけにせず、
数字で持てるところはintegerやdouble precisionも活用して
入れ物を冗長に持たせる方が、
どうせSQLで合計値や平均値などを算出するでしょうから
文字列→数値変換を省く意味で使うかなと。
インデックスもかけられそうだし。

それに、測定データなら測定できなかったときの値という特殊な保持の仕方もあるので、
単にNULLやゼロではない別の意味をもたせたフラグも必要だったりしますので。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/17 09:25

    ありがとうございます。
    確かにおっしゃるとおりですね。Variant型のような形式にする方法がありますね。
    VARCHARカラムに「値 > 1.23」 はちょっといかがなものか、と思っていました。

    キャンセル

+2

異なるスキーマを持ったログを同じテーブルに入れるのは誰も得をしません。
気温と電圧は比較が出来ないからです。
キーで分ける手間は開発にもDBサーバにもストレージに負荷をかけます。
このことから、現行の案1は今後問題になる可能性が高いです。

機械の種類ごとにテーブルを分けるのがRDBのやり方です。
この方法であれば、集計に問題が起こることはないと思います。

ただし、機械の種類が多すぎたり、非常に似た項目があるが機械毎に項目が少しずつ違う場合、
うまく管理出来ないということも考えられます。
その場合は、RDBは役に立ちません。苦手な事をさせても意味が無いと思います。

”日時・機械ID・明細”あたりのテーブルで、明細をプログラム側で集計しましょう。
データの表現はプログラム言語の方が豊かなので、管理もし易いと思います。

明細はXMLでもCSVでもどちらでも良いと思います。もちろん、案5でも良いです。
どちらにしろ集計は手間なので、DBの構造をいじる手間が減るだけマシにはあります。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/17 09:42

    ありがとうございます。
    様々なご意見をいただけて非常に助かります。

    > 異なるスキーマを持ったログを同じテーブルに入れるのは誰も得をしません。
    本当にそうだと思います。ここの部分に抵抗感がありご質問をさせていただきました。
    今回は「キー」「値」という意味では同じスキーマです、というかなり強引な方法をとりました。

    キャンセル

  • 2017/02/17 10:13

    キーと値が許されるなら、すべてのデータを1つのテーブルで管理可能です。
    でもそれは、RDBの使い方ではないです。
    (実際にKeyValuePairを基本としたNoSQLのDBMSは存在します。)

    きつい言い方ですみません。

    キャンセル

  • 2017/02/17 12:48

    いえ、とんでもないです。ありがとうございます。
    広く意見を募集したかったので、とてもありがたいです。

    キャンセル

+2

案1.よく見ますね。可もなく不可もなく、選択肢としては悪くないと思います。
他の人間が保守・運用するとしてもそこまで抵抗なく触れるでしょうし。
RDBでやるのなら、私もこれかな、と。

案2.個人的には無くはない、という感じですが、採用には気が進みません。
システムの製造者=運用・保守担当者なら問題ないのですが、そうでない場合、
自動生成の特性上、どのテーブルが何の機械のテーブルなのか、後年に分かりにくくなりそうです。
破棄された機械のテーブルのケアとかも煩雑な気がしますね。

案3.RDBの必要ないですよね、多分。個人的には無しですかね。

案4.この組み合わせは遭遇したことが無いので、すいませんがノーコメント(汗

案5.現状で意見があまり出ていないこれに言及させてもらいます。
同じく、用途がログ解析の案件経験が有り、
その際はMongoDBというNoSQLのDBとpentahoというツールで導入しました。

問題は、pentahoに関しては日本語の情報は多くないので、使用者・開発者双方から毛嫌いされるケースがあるかもしれません。
(私の時はpentaho kettle solutionsという本で勉強しました)
NoSQLのDB経験者も人口は多くはないのかな...すいません、その辺は分かりません。

ただ、そんな感じできちんと知識が無いと開発しずらいことから、
プロジェクトメンバー全員にRDBに比べればマイナーな知識が要求されます。
そのハードルさえ超えれれば、この手のDWH解析寄りの案件には非常にマッチするかと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/17 13:11

    ありがとうございます。
    MongoDBなど「KVSストアやNoSQLというものがある」ということは知っていたのですが、触れる機会がなく今回もなんとなく見送ってしまいました。
    これを期にMongoDBはじめ幾つか見てみようと思います。

    プロジェクトメンバーは実は私ひとりで、まわりのメンバーはRDBすら怪しい状況でして。。
    ですので私が抜けたら保守性云々抜きにして誰も保守できなくなるとは思っています。

    ですので知識や経験のある方々からこのようなお話を聞けてとても助かっています。

    キャンセル

+2

KEYに格納されているデータの種類はどれくらいあるのでしょう?あと機械の種類はどれくらいでしょう?
DWHの世界ではカラム数が数百あるテーブル定義も珍しくありません。
あと扱っているデータは蓄積していくもののようですからテーブル定義の変更はカラムの追加だけのような気がします。

先の種類の数によりますが、今後もずっとデータを記録していくなら、私なら一つのテーブルにするか機械の種類毎のテーブルにしますね。それからパフォーマンスとバックアップ単位を考えてデータベースを拠点毎や一定期間で切り分けます。

更にデータ量が億単位になる点から、RDBでの処理範囲を超えるかもとも考えます。

ビッグデータの分析の世界ではRDBではなくTSVファイル形式でデータを保存しておいて、それをテーブルに見立てて分析をかけることも珍しくありません。Apache Hadoop とかはその典型的なものでしょう。
Hadoopの機能をサービスとして提供してくれるAWSのEMRと呼ばれるプロダクトも便利です。
テラバイト単位のデータも扱ってくれるから良いですよ。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/20 09:31

    ありがとうございます。

    > KEYに格納されているデータの種類はどれくらいあるのでしょう?あと機械の種類はどれくらいでしょう?
    機械Aは200近くのキーがあり、機械Bは10程度です。今のところ2種類しかないですが、今後、制限なく増えていくことになっています(とは言え年2,3程度?なのかもしれません)

    外部のデータストアサービスを利用する、ということを全然考えていませんでした。自前でHadoopを立てるほどのものではないな、とは思っていたのですが外部のサービスを利用するという手もありましたね(今回のPJはお金ないので、それも無理ですがw)

    色々と自分に欠けている情報をいただけて非常に助かります。

    キャンセル

+2

とある情報を管理する製品が「GUIから定義を追加する事で各情報に任意のデータも追加できます」と言っていたので買ってみたら案3だった時があります。データは全て文字列型で、その他1、その他2…とたくさん用意されているだけで、

  • このデータは真偽値で→その他1で"TRUE"、"FALSE"という文字列で管理します!
  • このデータは整数値で→その他2で"0"、"1".. "100"という文字列で管理します!

という感じで、そしてVARCHAR(255)だし、255文字越える文字列データはどうするんだと聞いたら、分割して入れてくれとかふざけたこと抜かしました。二度とあの製品は買いません。


愚痴はここまでとして、サーバーの各情報(ディスク容量とか登録ユーザー数とか、メール総受信数とか色々)を集めてDBで管理するものを作った時、案5を採用しました。採用したDBはMongoDBです。1データの読み込みなら、WebからJSON受け取ってブラウザ側で処理するって方法だったので、JSONにしやすいMongoDBにしたという理由もありますが、サーバによってとりたい情報がバラバラだし、今後も増えていきそうだったのでNoSQLであるMongoDBにしたというのがあります。まだ、集めた過去の情報を閲覧できるだけで、本格的な分析とかの部分は作ってないので、集計とかするときはどんな感じと言われても、なんとも言えないのが現状ですが。

MongoDBの何が良かったというと、スキーマレスな所です。JSONっぽいBSONというものなので、階層化されたデータをそのまま扱えますし、一つ一つテーブル定義をしていく必要が無かったのは楽でした。ただ、ちゃんと最初にどういうデータ構造にするかを詰めておかないと、やっぱこれも!とかなってコード部分の方に出戻りがでるので、そこら辺はきちんとしたほうがいいようです。

もし、RDBMSという縛りがあるなら、Panzer_vorさんの方法をオススメします。任意のデータを追加できる系の結構メジャーなOSSではそのような構造を取っているようです(前に何かで見たんですが、何だったかまでは…たしか、uPortalだったかな?)。市販の製品は本当にできが悪いものが多くありますので、参考にしない方が良いと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/20 09:22

    ありがとうございます。
    出来上がったものが今のところトラブルを起こしているわけではないのですが、設計実装前にここでご相談しておけばよかったと後悔しています。
    MongoDB、調べてみます。色々と参考になるお話ありがとうございます。

    キャンセル

+2

MariaDBを使ってるならDynamic Columnを検討するのも手かもしれないです (使ったこはないです)。

https://mariadb.com/kb/en/mariadb/dynamic-columns/

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/21 15:43

    ありがとうございます。
    Dynamic Column、こんなものがあるとは知りませんでした。
    まさに今回にうってつけな感じですね。
    インデックスが無いのだけは残念ですが、非常に参考になる情報をありがとうございます。

    キャンセル

+2

質問5.他、経験談や参考情報などありましたらご教示ください。

データロガーというユースケースでぱっと思いついたのはDBはMongoDB、検索や集計用ににElasticsearch + Kibana です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/22 09:27

    ありがとうございます。
    MongoDBというご意見が結構多くでてきました。知らなかったのがお恥ずかしいです。
    Elasticsearch、Kibanaというものも初めて知りましたので、調べてみました。
    知識がないことによって随分非効率なことをしていたな、というのが実感です。
    ちなみに今回、グラフは AmCharts というものを使ってゴリゴリに実装しました。

    キャンセル

+2

質問5. 他、経験談や参考情報などありましたらご教示ください

こちらのみ回答します。

質問と同じようなセンシングデータを複数のデバイスから定期的に収集するシステムの経験から。

質問に記載の

データを元に各機械毎のグラフを描画したり全体で集計したりする。閾値判定も行う。

RDBは時系列データの処理はあまり得意ではありません。
TSDB系も視野にいれておくことを推奨します。
もちろんRDBでもしっかり設計されていて運用に問題が一切でていないのであればそれでも構いません。

用途にあっていないものを選定すると、次の問題が発生し、性能劣化、障害になる可能性があります
⇒インデックスの肥大化、オプティマイズに膨大な時間を要する
⇒ストレージの消費
⇒ディスクIO性能の限界
⇒削除コスト

また、グラフの描画や全体で集計については掘り下げて、具体的なデータの取得方法について整理しておいたほうがいいかもしれません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/23 09:49

    ありがとうございます。
    TSDBというキーワードを初めて知りました。
    ちなみに、現システムでは、MQTTでデータ受信、JavaでMariaDB(MySQL)に保存、PHP+Amchartsでグラフ表示、データの出力については期間を区切ってのCSV出力・グラフ表示、バッチでの閾値判定のみです。
    事前にこちらで質問し、知識をつけておけばだいぶ設計も変わっていただろうな・・・、という印象です。
    take88さんの仰る、MongoDB + Elasticsearch + Kibana を採用していたかもしれません。

    ⇒インデックスの肥大化、オプティマイズに膨大な時間を要する
     →今のところ時間と機械IDでのインデックスのみなので、時間はかかりませんが、おっしゃる通り既に肥大化し始めています。
    ⇒ストレージの消費
     →「上の判断」がまだなされていないため、今は削除していません。現状のままでも2年後に溢れます。 
    ⇒ディスクIO性能の限界
     →一応、システムとしての上限を測り、定義しています。
    ⇒削除コスト
     →かなり大きいです。「上の判断」がなされていない状態です(伝えてはありますが)。

    キャンセル

+2

私がこれを書き込む時点(2/25)で、回答数14で Panzer_vor さんの回答は+10までいってるのですが、まだベストアンサーがついてないので、とりあえず、参戦。
IoTと機械学習が大流行なので、今後この手の話はますます増えると思います。teratail としても、ぜひベストアンサーが欲しいところだと思います。

まず、一つのデータベースでいろいろなサービスを一気に実装する考え方(データベース中心アプローチ)をやめたほうが良いと思います。SOAあるいはマイクロサービスという考え方でアプリケーションを分解することを検討すべきです。たとえば、

  • ログを収集してとりあえず貯めるだけのサービス(極端な話、テキストでも良い。非同期性や完全性を確保するためのもの)
  • ログの多様性を吸収して変換し、データ内容に応じて他のサービスに配信(Contents Based Routing)するメッセージルータ(mule や servicemix のようなESB)
  • 集計や分析のためのデータウェアハウス(という言葉は古いのか?、Hadoop, RedShiftなど)
  • 集計結果をエンドユーザに提示するための、仮想的なデータベースサービス(すぐに例示できないけど、JBoss Data Virtualization とかかな)と JavaScript でリッチなUIを提供するWebクライアント(react, angularなど)

のようにサービスを階層化して分離します。サービスごとに変更のスピードが求められるもの、完全性が求められるもの、技術革新に追従する必要があるものなど要件がことなるので、Docker などでマイクロサービス化して、追加、交換などが容易になるように設計すべきだと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/27 09:18

    ありがとうございます。
    そういえば疎結合や独立性のことを全く考えていませんでした。
    新しい切り口でのご指摘ありがとうございます。

    そろそろベストアンサーを決定したいと思います。

    キャンセル

同じタグがついた質問を見る

  • データベース

    814questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

  • MariaDB

    360questions

    MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

  • データベース設計

    176questions

    データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

  • IoT

    67questions

    IoT(Internet of Things)とは、インターネットがコンピュータなどの情報・通信機器のネットワークだけでなく、世の中のある様々なモノに接続されて自動認識・自動制御・遠隔計測などの能力を備えることです。「モノのインターネット」と一般的にいわれます。

  • データ構造

    59questions

    データ構造とは、データの集まりをコンピュータの中で効果的に扱うために、一定の形式に系統立てて格納する形式を指します。(配列/連想配列/木構造など)