Help with MySQL solutions for site navigation

[latinomigs]

Hello,

 

My overall goal is to build a website generated by PHP with a MySQL database storing content. From experimenting I have a good idea of how to do most of it. Where I'm confused is with the organization and generation of site navigation.

 

My site nav is split into two sections:

 

- A primary nav bar with 6 links

- A left column with any number of secondary and tertiary navigation

 

An example of the output markup for the primary nav is:

 

<div id="priNav">

<ul>

<li><a href="#">Primary Link One</a></li>

<li><a href="#">Primary Link Two</a></li>

<li><a href="#">Primary Link Three</a></li>

<li><a href="#">Primary Link Four</a></li>

</ul>

</div>

 

And an example of the output markup for the secondary/tertiary nav is:

 

<div id="secNav">

<ul>

<li><a href="#">Secondary Link One</a></li>

<li><a href="#">Secondary Link Two</a>

<ul>

<li><a href="#">Tertiary Link One</a></li>

<li><a href="#">Tertiary Link Two</a></li>

</ul>

</li>

<li><a href="#">Secondary Link Three</a></li>

</ul>

</div>

 

 

My first attempt at organizing the db tables for navigation resulted in the following table structures:

 

dbt_nav_root

--------------------

id = The primary key for this table

cont_id = A foreign key that links to the primary key of the content table

text = The anchor tag text

is_parent = a boolean designating whether this table has child links

 

 

dbt_nav_child1,

dbt_nav_child2

--------------------

id = The primary key for this table

cont_id = A foreign key that links to the primary key of the content table

parent_id = A foreign key that links to the primary key of it's parent link

text = The anchor tag text

is_parent = a boolean designating whether this table has child links

 

 

I'm not sure how to measure the success of this structure because I can't find anything to compare it to. I guess it's been successfull because I was able to make it work, but I'm anxious to validate and improve my attempt through comparison and your advice.

 

So how do you guys handle this sort of thing?

 

[fenway]

This is the standard quest for storing hierarchical data... I personally prefer to have a single table, with a parent_id column which is NULL for the parent and points to the another record for any and all children.

[latinomigs]

Would there be any reason NOT to include a "level" column in that one table with a value that indicates it's position in the navigation hierarchy?

[fenway]

sure there is... you'd have to update it all the time, and everything related to it.  Right now, you could simply move a whole tree down a few levels by just updating a single uid field.