Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    db structure for navigational system

    Hello:

    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:

    [menu1]
    [section1]
    [item1_1]
    [subitem1_1_1]
    [subitem1_1_1_1]
    [...]
    [subitem1_1_2]
    [subitem1_1_3]
    [item1_2]
    [item1_3]
    [item1_4]
    [section2]
    [section3]
    [section4]

    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:

    Table Name: menu
    columns: id, title, permissions

    Table Name: section
    columns: id, menu_id, text, permissions

    Table Name: items
    columns: id, section_id, menu_id, text, img_path, href, permissions....

    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:

    Table Name: items
    columns: uid, item_id, section_id, menu_id, text, href, img_path, permissions, parent_id

    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!

    ~pman

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    The best option for infinite depth is a self-referencing table.

    You will have to also keep track of what level you are on.

    Finally you have to have a node number for your numbering scheme.

    Menu(MenuID, MenuParentID, LevelNumber, NodeNumber,...)

    It may need more thought, but I think that's it.
    Last edited by certus; 10-20-04 at 22:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •