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

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

ただいまの
回答率

91.23%

  • SQL

    1758questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データベース設計

    103questions

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

階層の深さが可変なツリー構造データを格納するDB設計

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 190

miyahan

score 2839

通信会社に勤めていてルーターやスイッチに収容されているユーザー回線数を集計する簡単なWebアプリを作っています。最終的にこんな感じの表を出力させたいです。

 rt_host   rt_port   sw1_host   sw1_port   sw2_host   sw2_port   users 
 RT01      Gi0/0/0   -          -          -          -          10    
 RT01      Gi0/1/0   SW01       Gi1/1      -          -          8     
 RT01      Gi0/1/0   SW01       Gi1/2      -          -          14    
 RT01      Gi0/1/1   SW02       Gi1/1      -          -          7     
 RT02      Gi0/0/0   SW03       Gi1/1      -          -          53    
 RT02      Gi0/0/0   SW03       Gi8/1      SubSW01    Gi0/1      11    

- になっているところがありますが、実は

  • RT --- Aプランユーザー
  • RT --- SW --- Bプランユーザー
  • RT --- SW --- SubSW --- Cプランユーザー

といった具合に契約プラン等によってネットワークの階層数が異なっており、頭を抱えています。

上表をそのままDBにするとNULLだらけになってしまう上に主キーも張れず悪手に思えます。- などのNULL代替ワードを使うことも検討しましたが、xxx_host カラムにホスト情報テーブルに対する外部キー制約をかけたいので避けたいところです。

このように深さが可変のツリー構造(複合主キーにするカラム数が可変)な場合どうテーブルを設計すべきでしょうか?具体的なスキーマではなく、考え方やデザインパターンの名称でも構いません。

条件

  • ネットワーク機器の階層は2〜4(例が雑多になるので本質問では最大3で取り扱っています)
  • 'SW03' の例のように、SW03 に直接ぶらさがっているユーザーが居るし、その下に 'SubSW01' があってそのさらに下にぶらさがっているユーザーも居る場合があります。と末端にだけデータノードが付いたシンプルなツリー構造ではありません
  • アプリの目的: ネットワーク機器のつながり情報の閲覧と、「SW01は全部で22ユーザー」のように任意カラムでのユーザー数集計
  • 環境: MySQL + Python (Flask + SQLalchemy)

以下は自分に考えてみた案です。長いので無視して構いません。

 代案1:通信機器のつながり情報とユーザー収容情報を分け再帰検索

neighbors テーブル:

 self_host(PK)   self_port(PK)   dest_host   dest_port 
 RT01            Gi0/1/0         SW01        Gi1/1     
 RT01            Gi0/1/1         SW02        Gi1/1     
 RT02            Gi0/0/0         SW03        Gi8/1     
 SW03            Gi8/1           SubSW01     Gi0/1     

users テーブル(ユーザーに隣接するホストにユーザー数情報を紐付け)

 last_host(PK)   last_port(PK)   users 
 RT01            Gi0/0/0         10    
 SW01            Gi1/1           8     
 SW01            Gi1/2           14    
 SW02            Gi1/1           7     
 SW03            Gi1/1           11    
 SubSW01         Gi0/1           23    

たとえば "SW03" で検索した場合、

  1. users テーブルを 'SW03' で検索:ヒット有り('SW03', 'Gi1/1', 11) → 一部結果取得
  2. neighbors テーブルの self_host を 'SW03' で検索:ヒット有り(SW03', 'Gi8/1', 'SubSW01, 'Gi0/1')
  3. users テーブルを 'SubSW01', 'Gi0/1' で検索:ヒット有り('SubSW01', 'Gi0/1', 23) → 一部結果取得
  4. neighbors テーブルの self_host を 'SubSW01', 'Gi0/1' で検索:ヒット無し → 下方向探索終了
  5. neighbors テーブルの dest_host を 'SW03' で検索:ヒット有り('RT02', 'Gi0/0/0', 'SW03', 'Gi8/1')
  6. neighbors テーブルの dest_host を 'RT02', 'Gi0/0/0' で検索:ヒット無し → 上方向探索終了、情報確定
  7. 上記で取り出した情報からHTMLの表を作成し表示

と処理が非常に煩雑になってしまいます。問い合わせ回数が多く、アプリ側で親・子・孫のどのレイヤーの情報なのか分からない上に、集計処理(GROUP BY + SUM)を自前で行う必要があり、「リレーショナルデータベースを使う意味があるのか?? もうNULLだらけでもいいんじゃないか!?」と思い始めています。

 代案2. 階層ごとにテーブルを分けて外部結合

最大階層数が3なのでつながり情報のテーブルを2つ用意し外部結合して検索

neighbors1 テーブル:

 self_host(PK)   self_port(PK)   dest_host   dest_port 
 RT01            Gi0/0/0         NULL        NULL      
 RT01            Gi0/1/0         SW01        Gi1/1     
 RT01            Gi0/1/1         SW02        Gi1/1     
 RT02            Gi0/0/0         SW03        Gi8/1     

neighbors2 テーブル:

 self_host(PK)   self_port(PK)   dest_host   dest_port 
 SW01            Gi1/1           NULL        NULL      
 SW01            Gi1/2           NULL        NULL      
 SW02            Gi1/1           NULL        NULL      
 SW03            Gi1/1           NULL        NULL      
 SW03            Gi8/1           SubSW01     Gi0/1     

users テーブル

(略)

実装は案1よりシンプルになりそうですが、それでも neighbors1 の self、neighbors2 の self、neighbors2 の dest とそれぞれJOINした3つの users テーブルの内容をアプリ側で合算する必要があり、なんだかなーという感じです。。。

以上、雑文で申し訳ありませんがアドバイスよろしくお願いします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+1

末端だけ属性を持たせたいということであっても、正規化を重視するならナイーブツリーで問題なさそうですけどね。
ただ、Mysqlは最新でないと再帰が使えないみたいですけど。

SQL: ナイーブツリーと再帰クエリ

正規化を緩める場合(ツリーでない場合)は、データは末端に合わせて、経路情報は配列型で持たせる方法。

DBMSが自由に選択できるんだったらpostgresをお勧めします。
再帰は使えるし再帰中で経路情報を配列で生成もできます。
また配列操作用の関数や演算子も充実しています。(例えば配列の要素数を取得する関数だったり)

追記

ツリーとユーザー情報を分けて、ユーザー情報はツリーとリンクさせるのが、正規化の観点からは理想ですね。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/21 16:26

    おっしゃる通りつながり情報とユーザー情報をわけつつ、つながり情報はナイーブツリーで表現するのがRDBらしくて良さそうですね。また、再帰検索が難しそうという懸念がありましたが、これも確かにポスグレを使えばアンチパターンを回避できますね。

    DBMSの変更を含め検討してみます。ありがとうございました。

    キャンセル

+1

木構造を表すときに代表的な「経路列挙モデル」は検討されたでしょうか?
↓はSQL関係の書籍で有名なミック氏のページです。
SQLで木と階層構造のデータを扱う(2)―― 経路列挙モデル

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/21 11:10 編集

    経路列挙・入れ子集合・閉包テーブルは軽く調べました。ですがどれも一長一短がありどれを選んで良いかわからず、さらに今回の場合は最大階層数が固定という条件があるので他のうまい方法をやっている方が居たら教えて欲しいなと思い投稿させていただいました。

    文字通り『経路」情報を記録するデータベースなので、経路列挙モデルと親和性が高そうですね。検討してみます。アドバイスありがとうございました。

    キャンセル

  • 2017/12/21 12:04

    別件ですが、複合主キーはよほどのことが無い限りお勧めできません。
    BプランのユーザーがAプランに変更したいとか、スイッチの収容台数を減らしたい時を考えてみてください。
    とても面倒ですよね?
    ユニークである制約が欲しいときは別途ユニークなキーを付ければいいだけです。

    キャンセル

0

入れ子集合モデルというのがあります。
http://www.geocities.jp/mickindex/database/db_tree_ns.html

自分の知る例では、RedmineのProjectやIssueの無制限の親子関係を表現するのに使用されています。
ネットワーク機器の繋がりを入れ子集合モデルで表現して、各ノードにユーザーを関連付けるのが良いと思います。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/12/20 23:35

    シンプルかつ効果的なモデルですが、今回のように親のこのポートに子のこのポートが繋がっているという経路情報を付与するのが難しそうですね。ホストノードの下にポートノードを1段足せば表現できそうですが、ノード名が RT01.slot1.port0 みたいに複数の情報を持たせるのはRDBらしくない気がしますし、かといってカラムを分けてホストノードの場合はslotとportは NULL ってのもそれでいいのかなという感じです。

    紹介いただいたURLのサイトを熟読してもう少し考えてみます。ありがとうございます。

    キャンセル

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

ただいまの回答率

91.23%

関連した質問

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

  • SQL

    1758questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データベース設計

    103questions

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