Storing hierarchical navigation data in a single table is a snap. Maintaining that data, however, can be a little bit of work (inserting sub items, removing sub items, etc.). What I wanted to share in this post is the solution i built for myself to perform navigation administration, where the navigation is stored in a single table with each record related to some other record in the same table. Bear in mind that I built it  for my eyes only, so isn't necessarily what I would give a customer to use. But, it didn't take an inordinate amount of time to build and it gets the job done, so I am kinda fond of it for those reasons. Without further adieux then, let's take a gander at it.

Following is a snapshot of the admin screen for maintaining the navigation. You'll notice it also incorporates a rudimentary sort of security.


So, in the scenario where I want to insert a sub-nav item somewhere, I simply add the new item's info to the "Add New" section at the bottom, making sure to designate its parent in the "Parent ID" column (choosing 'None' if this is a top level item). The sort order column is used to indicate where under that parent this particular item should show up. Right now, I have nothing in place to prevent me from having the same number twice, so I just have to look at the other children for this parent to determine what the sort order value should be.

Removing a nav item is as simple as checking the delete box. I have my update coded so that if a nav item is removed, its children are automatically removed as well.


Here are the relevant tables I use to store the navigation data (note that ev_nav has a relationship back to itself via an aliased instance of itself in the diagram):


If anybody is interested in getting a copy of the actual files (self-posting template, cfc, and mdb), feel free. Also, consider yourself warned, it's kinda fugly and wasn't meant for public review, but it should provide a good base for something prettier.