Re: Parts explosion with repeated subtrees

Actually, your code is so proprietary I had a hard time reading it; is this what you meant to post, if you used ISO-11179 specs, had keys, constraints, etc. ?

CREATE TABLE Menu
(menu_id INTEGER NOT NULL PRIMARY KEY,
 item VARCHAR(25) NOT NULL,
 parent_id INTEGER NOT NULL

           REFERENCES Menu(menu_id),
 level_id INTEGER NOT NULL);

 INSERT INTO Menu VALUES (1,'Eggs Benedict', 0, 1);
 INSERT INTO Menu VALUES (2, 'English Muffin', 1, 2);
 INSERT INTO Menu VALUES (3, 'Canadian Bacon', 1, 2);
 INSERT INTO Menu VALUES (4, 'Egg', 1, 2);
 INSERT INTO Menu VALUES (5, 'Béarnaise sauce', 1, 2);
 INSERT INTO Menu VALUES (6, 'Dill', 5, 3);
 INSERT INTO Menu VALUES (7, 'Hollandaise sauce', 5, 3);
 INSERT INTO Menu VALUES (8, 'Egg', 7, 4);
 INSERT INTO Menu VALUES (9, 'Lemon Juice', 7, 4);
 INSERT INTO Menu VALUES (10, 'Butter', 7, 4);

Sorry, but a few decades of writing and working with standards makes me a bit complusive. Now, another human being can read and use your code to test things in any Standard SQL!!

Having done that work, this is an Adjacency model and it has the same problem my nested set model does. Namely, 'Béarnaise sauce' and 'Hollandaise sauce' explosions have to be repeated in EVERY recipe. In your model, they will get various level numbers, in mine, they get various (lft,rgt) pairs.

What I want is a way for them to appear once in the schema and expanded in a VIEW as tehy are used. Received on Tue Dec 17 2002 - 21:54:33 CET