Advice on DB table setup to store higherarchy for menu in a CMS, forum, so on.

[horseatingweeds]

I'm working on a CMS specific for my own use in running a website. It uses MVC design pattern. One thing I'm not sure of is the best way, for something with a complicated 'file' hierarchy such a groups of articles split into sub article or a forum, to establish the organization in a DB.

 

Here's what I mean. Say you have several articles. One is about about... painting.

Painting

I. Brushes

    a. cleaning

    b. selecting

        i. latex

        ii. oil

      c. stoke technique

        i. left hand

        ii. right hand

II. Buckets

    a. safety

    b. bucket varieties

        i. Metal

        ii. plastic

    c. bucket care

        i. polishing

        ii. powder

 

..... and so on. Each of these articles would be set up using the same code and templates. Currently I like using one table for an object's essentials, like ID time stamp, and url; and a second table tide to the first one  by the ID which simple stores a key-value for the values in each object.

 

I'm just struggling to figure a good plan for storing the hierarchy for the menu next to each article. Any good ideas?

 

[Zwiter]

You can just copy the way some forums(phpbb) or some CMS manage there database:

a table menu:

menu id

parent id

left id

right id

//then any data concerning the menu : title, url...

 

table article :

article id

menu id

title

content

//any other informations: date, autor...

 

keep in mind that: is you have a relationship between 2 element wich is: to the 1st element corresponds only one of the 2nde element, AND to the 2nde elemenet corredponds only one of the 1st element. These 2 elements SHOULD TO BE in the same table, whatever it is: menu, article, or another one.

Z.

[horseatingweeds]

Zwiter, thanks,

 

I was thinking about this way but couldn't figure out how to make it work. I've thought about it some more though and it should. I just need to figure out exactly how.