Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language

Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Best optimal table design for parent-child tables


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:

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.



Message posted via SQLMonster.com

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)

and as many levels as you want


"zwieback89 via SQLMonster.com" <u28634@uwe> wrote in message

>> 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.

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)


Message posted via SQLMonster.com


No, this book is significantly better and will give real workable solutions
that work in the real world and on Microsoft SQL Server.


Tony Rogerson, SQL Server MVP
[Ramblings from the field from a SQL consultant]
[UK SQL User Community]

"--CELKO--" <jcelko@earthlink.net> wrote in message


Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc