hey there, perhaps an article would help more.
I've done hierarchical tables before but now what I'm wanting to do approaches the "Theory of Everything" (TOE) or "Grand Unified Theorem" (GUT) for databases.
suppose you have boxes 1 through 9
and suppose you have bags a through z
and suppose you have containers alpha through omega
that's three levels of possible nesting.
inside bag a I want to put box 1, 2, and 3
inside bag b I want to put box 1, 3, and 9 (note it's perfectly ok for box 3 to be in two places at once)
and and b are in container alpha
more to the point, these groupings also represent sub-assemblies for my indended application - not so much bags and boxes.
those relationships would be as follows:
parent child
------- -------
alpha a
alpha b
a 1
a 2
a 3
b 1
b 3
b 9
I have no problem with a & b being, say, a conceptual sub-assembly or grouping versus a "real" object. In that case I can see the assembly visually from that table
HERE IS THE PROBLEM:
now, I want to create an entirely new assembly beta with sub assembly a and c. a and c contain duplicate or overlapping (with the first assembly) instances of boxes 1 through 9
and, a is an assembly in alpha AND beta - though conceivably it could contain 90% of the same boxes but just a slight difference - and though that's a real-world issue it's also a significant part of the problem
HELP!!!!! At this point I can still add the relationships to the join table above, but it's no longer reliably readable.
Of course any idiot could add an id column to the left and have assembly alpha's objects all have that key (say 1) and beta all have 2. But that doesn't work if I want to build a super assembly from alpha and beta.
If you see where I am going with this, please give me some ideas toward retaining all structural concepts in the most elegant format. Thank you!
Sam
