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

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

新規登録して質問してみよう
ただいま回答率
85.50%
SQL

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

データベース設計

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

Q&A

解決済

3回答

9319閲覧

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

miyahan

総合スコア3095

SQL

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

データベース設計

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

2グッド

2クリップ

投稿2017/12/20 12:02

編集2017/12/21 02:34

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

rt_hostrt_portsw1_hostsw1_portsw2_hostsw2_portusers
RT01Gi0/0/0----10
RT01Gi0/1/0SW01Gi1/1--8
RT01Gi0/1/0SW01Gi1/2--14
RT01Gi0/1/1SW02Gi1/1--7
RT02Gi0/0/0SW03Gi1/1--53
RT02Gi0/0/0SW03Gi8/1SubSW01Gi0/111

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

  • 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_hostdest_port
RT01Gi0/1/0SW01Gi1/1
RT01Gi0/1/1SW02Gi1/1
RT02Gi0/0/0SW03Gi8/1
SW03Gi8/1SubSW01Gi0/1

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

last_host(PK)last_port(PK)users
RT01Gi0/0/010
SW01Gi1/18
SW01Gi1/214
SW02Gi1/17
SW03Gi1/111
SubSW01Gi0/123

たとえば "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_hostdest_port
RT01Gi0/0/0NULLNULL
RT01Gi0/1/0SW01Gi1/1
RT01Gi0/1/1SW02Gi1/1
RT02Gi0/0/0SW03Gi8/1

neighbors2 テーブル:

self_host(PK)self_port(PK)dest_hostdest_port
SW01Gi1/1NULLNULL
SW01Gi1/2NULLNULL
SW02Gi1/1NULLNULL
SW03Gi1/1NULLNULL
SW03Gi8/1SubSW01Gi0/1

users テーブル

(略)

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

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

umyu, KSwordOfHaste👍を押しています

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

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

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

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

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

追記

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

投稿2017/12/21 04:29

編集2017/12/21 05:40
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

miyahan

2017/12/21 07:26

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

0

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

投稿2017/12/21 01:55

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

miyahan

2017/12/21 02:10 編集

経路列挙・入れ子集合・閉包テーブルは軽く調べました。ですがどれも一長一短がありどれを選んで良いかわからず、さらに今回の場合は最大階層数が固定という条件があるので他のうまい方法をやっている方が居たら教えて欲しいなと思い投稿させていただいました。 文字通り『経路」情報を記録するデータベースなので、経路列挙モデルと親和性が高そうですね。検討してみます。アドバイスありがとうございました。
hihijiji

2017/12/21 03:04

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

0

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

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

投稿2017/12/20 12:43

hichon

総合スコア5737

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

miyahan

2017/12/20 14:35

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問