HELP! Storing Menu data (tree shaped data) in a relational DB

Here's the scenario. I'm creating toolbar that will have menus that pop up when a button is pressed. The data that goes in the menus comes ultimately from a Table called Merchants. So I'm thinking the ER would go something like this:

(Toolbar) -< (ButtonRow) -<Button - Menu -< MenuItems >- Merchants

                                                             |_V

Of note, a Button can only have one Menu (the TopLevel Menu), but a Menu can have one or more other Menus and/or one or more MenuItems. A MenuItem can only have one Merchant, but one Merchant can be in zero or more MenuItems.

Since there would only be one Toolbar I couldn't see creating a table for it. The ButtonRow data in being stored in the Button table as an Int. Each table has a Parent field that acts as the Foreign key to relate it to its Parent. My first inclination was to create a single Menu table where the Parent field would reference the MenuID of another record in the same table. But there is data that the Buttons have that the menus don't (besides ButtonRow, there's the URL to the images that the button would have), so there would have been lots of empty cells in the table. Was that wrong?

Another problem that I run into is maintaining Referential Integrity. There are two types of Menus: Sub-Menus whose Parent's are other Menus, and Top-Level Menus whose Parent's are Buttons. So to distinguish one from the other I put a Boolean field in Menus called TopLevel and set it to true for those that are top-level menus. The problem that this creates is that I can't create a FK relationship to Buttons, since some of the records in Menu refer to Menu itself.

ARRGHH. And I still hafta figure out how the MenuItems table is gonna work, and all the queries, and how I'm gonna do this that and the other thing.

So any help,comments, or references you might share would be greatly appreciated. This is the first time I've had to model this type of data and with XML becoming all the rage I would really like to get my mind around this the right way, instead of hacking it over and over.

THANKS! PS: Hell, if nothing else I just saved myself $75 bucks and my shrink a headache. ; -)

--


James Carroll