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


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

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

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