Accessで簡易E-BOM作りたい。テーブル設計教えてください。

Question

Accessで簡易E-BOMを作りたい。

データベース設計を身近に相談できる人がいないので教えてください。
Accessで簡易E-BOMを構築しています。

基本的には製品番号に部品番号がN数ぶら下がるように下記のようなテーブル構造を考えました。

しかし、製品番号に別の製品番号がくっつくこともあるため、t_構成部品一覧の部品番号に製品番号が入る場合もあり、ここの設計で困っています。

［製品①と製品②をジョイントでつなげて製品③を作る］

t_製品番号とt_部品番号をUNIONクエリでまとめて、それをt_構成部品一覧と外部結合させて登録用フォームを作成すると更新や追加ができず、t_製品番号を複製してとt_製品番号1とt_部品番号を参照整合性を付けると片方に存在しない番号はエラーではじかれてしまう。

［UNIONクエリでt_製品番号とt_部品番号を結合させたクエリ1］

t_構成部品一覧の部品番号にはt_製品番号かt_部品番号のどちらに番号があればOKみたいなかたちで設計することはできませんか？

また、こういった問題が発生した場合、どうやって設計を行うのか教えていただけませんか？
よろしくお願いします。

Answer 1

製品番号と部品番号の番号体系が別で重複しないなら不要ですが、重複する場合を考慮してその識別は項目として持っていた方が良い気がします。
その場合、Accessの特性上冗長になりますが、joinする項目はそれぞれのテーブルに持たせた方が良いです。

t_構成部品一覧(製品番号,識別,構成部品番号)
t_製品番号(識別,製品番号)
t_部品番号(識別,部品番号)
※識別=(0:部品,1:製品)などとする。

上記構成を、［UNIONクエリでt_製品番号とt_部品番号を結合させたクエリ1］と同等の関係は以下のようなSQLで表わせます。

select *
from ((
     t_製品番号 as t1 
     left join t_構成部品一覧 as t2
     on t1.製品番号=t2.製品番号
     ) left join t_製品番号 t3
     on t2.識別=t3.識別 and t2.構成部品番号=t3.製品番号
     ) left join t_部品番号 t4
     on t2.識別=t4.識別 and t2.構成部品番号=t4.部品番号

基本的には、元の構成でもunionクエリーを使わずに同じ表現はできると思います。
unionクエリーを使用すると更新できないクエリーになるので、それが根本的な理由だと思います。