Sql Server Programming :: Best optimal table design for parent-child tables


	Best optimal table design for parent-child tables Hi, I in the process of building an IT web site. I am building a table for the navigation menu. What is the best optimal design for the table? What I have now is: HOME NEW Hires Checklist IT Groups IT News and Announcements Monthly Group Meetings Content Management IT Bragging Board However some of the above have sub-menus. For instance, IT Groups has another 5 sub-menues under it. My table now is: Table: MenuListing MenuID (PK int) MenuListing (varchar (150)) Table: SubMenuListing SubMenuID (PK int) SubMenuListing (varchar (150)) MenuID (int 4) Is this an optimal design for a possibilly growing menu? Now the menu is only 2 levels deep. It could grow deeper. Please come back with your suggestions. Thanks. -- --------------------- zwieback89 Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007... I would suggest to introduce parent id For main level parent id will be 0 for levels 1-n parent id will point to menu item, which parents specified menu items. Then you can have just one table menuid (PK) parentmenuid (index) menutext and as many levels as you want HTH Alex "zwieback89 via SQLMonster.com" <u28634@uwe> wrote in message news:7341bf2d07ba8@uwe... - Hide quoted text - > Hi, > I in the process of building an IT web site. I am building a table for the > navigation menu. > What is the best optimal design for the table? > What I have now is: > HOME > NEW Hires Checklist > IT Groups > IT News and Announcements > Monthly Group Meetings > Content Management > IT Bragging Board > However some of the above have sub-menus. For instance, IT Groups has > another > 5 sub-menues under it. > My table now is: > Table: MenuListing > MenuID (PK int) > MenuListing (varchar (150)) > Table: SubMenuListing > SubMenuID (PK int) > SubMenuListing (varchar (150)) > MenuID (int 4) > Is this an optimal design for a possibilly growing menu? Now the menu is > only > 2 levels deep. It could grow deeper. > Please come back with your suggestions. > Thanks. > -- > --------------------- > zwieback89 > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007... >> Now the menu is only two levels deep. It could grow deeper. << Do yoU have a copy of TREES & HIERARCHIES IN SQL? You need it. -----------------------------------------------Reply----------------------------------------------- Thanks for the response. This is what I came up with. ---------------- Create Table MenuListingTest( MenuID int Primary Key, MenuListing varchar(200), MenuParentID int ) Insert into MenuListingTest Values(1, 'Home', 0) Insert into MenuListingTest Values(2, 'New Hire Checklist', 0) Insert into MenuListingTest Values(3, 'IT Groups', 0) Insert into MenuListingTest Values(4, 'IT News and Announcements', 0) Insert into MenuListingTest Values(5, 'Monthly Group Meetings', 0) Insert into MenuListingTest Values(6, 'Content Management', 0) Insert into MenuListingTest Values(7, 'IT Bragging Board', 0) Insert into MenuListingTest Values(8, 'Database Admin', 3) Insert into MenuListingTest Values(9, 'Change Management', 3) Insert into MenuListingTest Values(10, 'EBS', 3) Insert into MenuListingTest Values(11, 'Oracle GI / SPR', 3) Insert into MenuListingTest Values(12, 'IT Infrastructure', 3) Insert into MenuListingTest Values(13, 'IT Quality Compliance', 3) -- --------------------- zwieback89 Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007... -----------------------------------------------Reply----------------------------------------------- No, this book is significantly better and will give real workable solutions that work in the real world and on Microsoft SQL Server. http://www.amazon.com/Expert-SQL-Server-2005-Development/dp/159059729X -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "--CELKO--" <jcelko@earthlink.net> wrote in message news:1181073611.111053.173450@q66g2000hsg.googlegroups.com... - Hide quoted text - >>> Now the menu is only two levels deep. It could grow deeper. << > Do yoU have a copy of TREES & HIERARCHIES IN SQL? You need it.