ツリー構造をRDBで表現するには？

質問

ACCESSのようなRDBで階層構造を管理する場合のテーブル構造の定石ってあるんでしょうか。
「部品表」のようなものをデーベース化したいのですが、イマイチしっくりきません。
例えばAという部品はＢとＣという部品で構成されています。ＢはＤとＥとＦ。
ＦはＡとＥで成り立っているというようなものです。
この階層がどれくらい続くかは、任意に決定されねばなりませんし、もちろん各部品の親子関係もどこからでもできるだけ解かり易い形で表示可能にしなければなりません。
Windoｗsのエクスプローラーのような表現が理想なのですが、各階層毎には表現できても、全体の繋がりがどうも・・・
こういう場合によく使われる基本的なテーブル構造というのがあれば教えて下さい。

回答No.3

ARC · ベストアンサー

>特にコレクションのゴミ掃除の部分なんかはチンプンカンプンです。

あんまり気にしなくても良いんですが(「ゴミ掃除」に関してはそのまま書き写すだけで動作しますんで)、ちょっとだけ解説します。

えと、まず、一つの部品に付きフォームが１枚ですんで、複数の部品を同時に表示するためには、それだけの枚数のフォームを用意しなければならない。

あらかじめフォームを１０個も２０個も作っておくのはあまりにも非現実的ですんで、最初に汎用的なフォームを１枚作っておいて、必要に応じてその複製を画面に表示するようにしよう、と。

で、フォームの複製を作る(*1)のは良いんですが、どこかにそれを保存しなくちゃならないんですよね。
そこで、BuhinWins って名前を付けたコレクションを作成(*2)し、新しくウインドウを作るごとにそこに登録(*3)するようにしたわけです。
*1 Set frmBuhin = New Form_F部品
*2 Public BuhinWins As New Collection
*3 BuhinWins.Add frmBuhin

ここまではいいんですが、そのウインドウを×して閉じた後でも BuhinWins 内のデータは勝手に消えてくれず、無効なウインドウのデータがゴミとして残ってしまうのです。

例えば、１０枚のウインドウを開いたとすると、BuhinWinsには１０枚分のデータが登録されます。
そのあと4枚のウインドウを閉じた場合、画面上に表示されているウインドウは6枚でも、BuhinWinsには有効なデータ６枚分に加えて無効なデータが４枚分が登録されています。

ってことで、「ゴミ掃除」の部分で無効なデータを見つけては、これを削除するってことをやってるわけです。

>ところでVBのツリービューコントロールというのは、手に入れさえすれば、初めてでも割と簡単に使えるものなのでしょうか？

んーと、VB(A)初級者には若干キツイ部分もあるかもしれません。
TreeViewの取り扱い自体が多少複雑なのに加えて、これに関するヘルプの項目もあんまり親切じゃないんですよね～。
ヘルプの読み方のコツが身についてないと、概要を理解するだけでも苦労するかもしれないです。
ただ、他の方法で同種のインターフェースを作るのに比べたら、遥かに楽な方法であることは断言できます。

VBについての市販本はたくさん出版されているので、まずは大型の本屋を物色して、TreeViewについて詳しく書かれている本を買ってきて、それを見ながら勉強するのがいいのではないかと思います。

>ある製品を指定することで、その製品の全部品を一括でレポートにすることは可能でしょうか？

可能ですが、こっちもVBAの世話になる必要があります。
印刷用の一時テーブルを作っておいて、そこに部品一覧をぶち込みます。
アルゴリズムの概要だけ書くと、

(テーブルの構成)
一時テーブル
　コード(テキスト型)
　親部品ID
　部品ID
　　:

dim RS as Recordset　'一時テーブル
Dim RSParts　'構造テーブル

'一時テーブルの中身をクリア(略)
　　：
SET RS=OpenRecordset("一時テーブル")'一時テーブルを開く

'カレーうどん自体を、RSに追加
　RS.AddNew
　RS![コード]="01"
　RS![親部品ID]=null
　RS![部品ID]="カレーうどん"
　RS.Update

RS.MoveFirst
Do Until RS.EOF 'レコードの末尾に達するまで繰り返す
　　BuhinID=RS![部品ID] '現在のレコードに書かれている部品IDを取得
　　Code=RS![コード]

　　'BuhinIDで指定された部品の構成部品を検索する
　　Set RSParts=OpenRecordset("SELECT * FROM 構造テーブル WHERE 部品ID=" & BuhinID)
　　　counter=1
　　　do until RSParts.eof
　　　　RS.AddNew
　　　　RS![コード]=code & format$(counter,"00")
　　　　RS![親部品ID]=BuhinID
　　　　RS![部品ID]=RSParts![構成部品ID]
　　　　RS.Update
　　　　RSParts.MoveNext
　　　　Counter=Counter+1
　　 loop

　　RS.MoveNext'次のレコードに移動
Loop

こんな感じですね。
[コード]フィールドで並べ替えを行ったり、階層の深さを測ったりします。

うまく動いたとすると、以下のように[コード]が生成されているはずです。
コード 部品ID
01 カレーうどん
0101 うどん
0102 カレー
010101 水
010102 うどん玉
010201 カレー粉
　：
01020101 ターメリック
01020102 ガラムマサラ

相変わらず長文だ～

お礼

すっばらいしいです。
おかげさまでこのコードを基本に多少アレンジすれば、私の悩みはほぼ解決しそうです。
まあ、私の場合アレンジ力が問題ですが、なんとか試行錯誤でやってみます。

ツリービューコントロールについては、アドバイス頂いた通り、気長に基礎的なところからやってみます。
というかこの一括表示と前述いただいた無限階層川下りをうまく絡ませて併用すれば、ツリービューまで取り入れなくとも、十分階層表現できそうですし現状大きな問題はないと感じています。

試行錯誤の途中で挫折しました折にはまたよろしくお願いします。
ありがとうございました。

回答No.2

ARC

>それから、困った点は、ドライカレーも部品になり得る(中略)部品の概念と完成品の概念はやはり区別しなければなりません。

これは、[完成品]フィールド(Yes/No型)と[部品]フィールド(Yes/No型)を作ってやればいいんじゃないでしょうか。
ドライカレーは完成品でもあり、部品でもあるってことで、両方にチェックを付けるとか。

>ところがこれが三層構造くらいですと問題はないのですが、いくつも連なってくると・・・

この辺がメンドクサイんですよねぇ。
とりあえずVBのツリービューコントロールを流用すると、手っ取り早くエクスプローラライクなインタ－フェースを作れると思います。

フォームで作る場合には、メインフォーム→サブフォーム→サブサブフォーム・・・ ってな作り方はダメですね。仰るように、階層が増えると行き詰まってしまいます。

この問題は、任意の部品及びその構成要素を表示する汎用的なフォームを作っておき、構成要素をクリックするたびに、このフォームのインスタンスを新しく作成、表示する、っていうふうにして解決します。

概略だけ書きますと、

1:フォームに[部品名]等のテキストボックスと、[構成部品]サブフォームを貼り付ける。(フォーム名は[F部品]とでもしておく)

2:同フォームに
public sub RefreshData(BuhinID as long)
　　'指定された部品のデータをフォームに表示する
end sub
ってな感じのコードを書く。

3:標準モジュールを作成し、宣言セクションに
Public BuhinWins As New Collection
って書く。

4:F部品の構成要素クリック時に
Private Sub コマンド1_Click()
　Dim i As Long, dummy As Variant
　Dim frmBuhin As Form_F部品
　'コレクションのゴミ掃除
　On Error Resume Next
　For i = BuhinWins.Count To 1 Step -1
　　dummy = ""
　　dummy = BuhinWins(i).Name
　　If dummy = "" Then
　　　BuhinWins.Remove i
　　End If
　Next i
　'フォームのインスタンスを作成して、コレクションに追加
　Set frmBuhin = New Form_F部品
　frmBuhin.RefreshData Me.構成部品ID
　frmBuhin.Visible = True
　BuhinWins.Add frmBuhin
End Sub

ってな感じのコードを書く。
以上。

このようにすると、構成部品をクリックするたびに、その部品を表示するウインドウが新しく開きます。
新しく開いたウインドウに更に構成部品が含まれていたとしても、それをクリックすればまた別のウインドウが開きます。

補足

早速のご回答ありがとうございます。
残念ながら、私はコードを自在に駆使できるレベルにないので、このコードをそっくりそのままコピーして使うことはできても、応用がききません。
おおよその組立は理解できるのですが、特にコレクションのゴミ掃除の部分なんかはチンプンカンプンです。
でも、VBAを駆使すれば無限階層表示も可能ということがわかっただけでも大きな収穫です。
挫折中だったコードのお勉強を再開せねばと痛感しました。
ところでVBのツリービューコントロールというのは、手に入れさえすれば、初めてでも割と簡単に使えるものなのでしょうか？
それと甘えついでにもう一つ。
ある製品を指定することで、その製品の全部品を一括でレポートにすることは可能でしょうか？
レポートの表記としては、例えばカレーうどんを指定すると

階層　　　親部品ID 　　部品ID 　　重量　　　

　1　　　カレーうどん　　うどん　　　200
　1　　　カレーうどん　　カレー　　　100
　2　　　うどん　　　　　　水　　　　100
　2　　　うどん　　　　　うどん玉　　100
　2　　　カレー　　　　　カレー粉　　 50
　2　　　カレー　　　　　肉　　　　　 20
　2　　　カレー　　　　　水　　　　　 30
　3　　　カレー粉　　　　ターメリック 40
　3　　　カレー粉　　　ガラムマサラ　 10

というような一括表形式が理想です。これができれば、部品の所用量計算が容易になりますし、ある部品から一直線に製品までさかのぼることもできると思うのですが。

回答No.1

ARC

部品テーブル
　部品ID
　部品名
　　:

構造テーブル
　部品ID
　構成部品ID
　数量
　　:

のようなテーブルを作ればいいのでは？

例えば、ドライカレーのレシピなら
部品テーブル
部品ID　部品名　単位
1　ドライカレー　皿
2　ご飯　合
3　カレー粉　g
4　植物油　cc
5　米　cc　
6　水　cc
7　コリアンダー　g
8　ターメリック　g
9　チリパウダー　g

構造テーブル
部品ID　構成部品ID　数量
1　2　1　
1　3　30
1　4　15
2　5　180
2　6　270
3　7　0.2
3　8　0.3
3　9　0.5

として表現可能です。
ドライカレー１皿は{ごはん1合, カレー粉30g, 植物油15g}で出来ていて、ごはん１合は{米180cc, 水270cc}、カレー粉1gは{コリアンダー0.2g, ターメリック0.3g, チリパウダー0.5g}で出来ていることが見て取れると思います。
ちなみに分量は滅茶苦茶です(^^;

部品にどれだけ階層が増えようと上記の二つのテーブルだけで表現できますし、任意の部品の構成部品を調べることも容易です。

補足

やっぱりこれっきゃないですよね。
実は、私も基本構造はこの考え方でやってはいるのですが、どうもですね。
何がどう悪いのかはっきり説明のつかない点が、一番問題ですね。
考えが整理できてなくて申し訳ないです。
えーとまず、この構造ですとおっしゃる通り何層構造になろうが、任意の部品の親子構成は簡単に表現できます。クエリー一つ作るだけです。
ところがこれが三層構造くらいですと問題はないのですが、いくつも連なってくると親子関係のそのまた親子関係そのまた親子関係というように系列で表現せなばならず、縦横無尽に系列をさかのぼる、下る、あるいは一度に頭から尻尾まで系列を表示するというような必要が出てきました。
これを表現するために、部品一覧フォームからある部品を選択すると、その子部品構成を表示するポップアップを作り、このポップアップのある部品を選択するとさらに孫部品を表示するポップアップが開くというような仕掛けを作って四苦八苦しているのですが、いくら作っても正直きりがありません。
さらに系列を一度に表示させようとすると、サブフォームやサブリポートを無理矢理駆使しまっくって余計混乱させている有り様です。
それから、困った点は、ドライカレーも部品になり得ることで、例えば「ハンバーグドライカレーうどん」になってしまうのです・・
にもかかわらず、部品の概念と完成品の概念はやはり区別しなければなりません。

というような問題が山積みしてまして、頭はパニック。うまく説明できなくてごめんなさい。
テーブル構造は問題ないとすると、やはり私のユーザーインターフェイス作成能力の部分が問題ですね。
このテーブル構造で系列を表現するのにいい仕掛けがありましたらぜひご教授下さい。