I have a navigational system which I would like to store in a database and I want to know how I can structure my database so that it will be able to, in theory, keep track of an infinite level navigational system. Let me give you an example of what I am trying to model. Below is a sample menu:
Ok think of the above navigational menu as a web menu. Each section in the first level (I am not counting the [menu] part as a level) should ideally be able to have an infinite number of [items]. And each [item] should be able to have an infinite number of [subitems]. The way that I see the database set up so far is kinda like this:
Now I have a few more fields in each table but I think you guys get the basic idea. I feel pretty confident about my menu & section tables. However the problem lies in the "items" table, it does not make sense for me to keep track of each additional level of items or sub items in separate tables otherwise I could potentially (although it would be a menu with a poor taxonomy) end up with an infinite number of tables, so what i want to do is keep track of all items AND subitems in ONE table alone. is this possible? I thought of doing something like this:
In this case the uid would be a unique id for each row in the table. And the parent_id would be a reference to the uid column, in that way you could keep track of each item's parent. HOWEVER, if you notice above, how I keep track of each level by numbering it, (ie. subitem1_1_1), well I need to use that numbering system in my implementation code. So i can't end up chasing links to other fields since I have to be able to build the "1_1_1" part for each item in the table in one simple call. Is there a way to do this? any help at all would be greatly appreciatted. thank-you!