>Gar,
>
>>
>>Nope -- it can be done with just the modest denormalization of propagating the parent key down the entire child, grandchild etc hiearchy. <
>
>Let me stop you here. The objection is that the denormalization must occur from each level to all levels lower, which would become unmanageable. This is the only way to have a general solution in which any 1:M:M... form could "jump in" at level X and display/edit levels X, X+1, and X+3.
>
>Consider your example of the Customer:Sales Order:Line Items. Let's extend it one more level to Customers:Sales Orders:Line Items:Shipping History. Assuming we have implemented a filter-based solution, then it should be easy to make a form that starts with customers and displays the complete 4 level 1:M:M:M heirarchy. However, tommorrow we may find a need to produce an abbreviated form that contains only Sales order:Line Items:Shipping History heirarchy. In that case, the surrogate key in Sales Orders would also have to be denormalized at each lower level.
>
>In conclusion, then, a truly n-level general solution would require the surrogate key in every table except the bottom to be propogated all the way down. Hence my original statement that filtering causes the surrogate keys to multiply geometrically.
>
>I agree that the solution works, but to my sense of aesthetics this denormalization appears too radical and leaves me still seeking a more palatable compromise.


No you do not have to propagate the keys down other than the parent key. The reason is that even if you are doing an abbreviated form *You open the parent table* and make the form parent *behind* the scene, while making it look to the user as though a child or grandchild or whatever is the parent. If you are opening a grandchild as an apparent parent, then you have to open both parent and child behind the scenes. Or am I missing something?