DB structure for the topics of a website

[Anon]

OK! I'm beating my brains out on this one and when I do that it usually means the answer is simple. So with that in mind I'll go forth with risk of total embarrassment. :-)

I'm developing my first database driven web site with the following idea.

1. Users can write articles basically about anything.

2. I want to organize the articles based on topics. Which can extend to any number of subtopics.

Here's the Problem:

I need to create a table for the topics. And the topics should be tracable through the topic uid which would be saved with the article.

For example:

Some one would like to write an article for the following topic and sub topics.

Products:Cars:Chevrolet:Corvette

So what should the table look like and how can I retrieve the data from the topic uid saved with the article? I´ve read somewhere about parent/child system but I can't recall how it works.

Please.....any ideas are welcome.

-Scott

[yelvington]

In your topic table:
topicid is an auto-incremented unique int
parentid is an int, can be null
label is varchar of some reasonable length
(you also could have a description if you so desire)

Imagine that your data looks like this:
life
animal
mammal
primate
human
reptile
vegetable
(etc)

life is the root of the tree; its parentid is null.
animal and vegetable have their parentid set to life's topicid.
mammal has its parentid set to animal's topicid. And so on.

In your articles table, you would simply need a column to hold the topicid to which the article is mapped.

To find all of a topic's subtopics, you would simply select ... where parentid = the current topicid.

To find all of the articles on a topic, obviously you would query the article table for articles with the current topicid.

To create the "bread crumb trail" navigation that you see on Yahoo, et cetera, you would assemble the string from right to left, starting at the current subtopic level. You look at the parent, then the parent's parent, etc., recursively until you climb all the way back to a record with no parent (which is the root of the tree).

In this example, human's parent is primate; primate's parent is mammal; mammal's parent is animal; animal's parent is life.

You might then print out the trail:

life / animal / mammal / primate / human

[Anon]

Thanx Steve,

I was hoping you would come to my rescue.

-Scott

[yelvington]

Argh. I just saw that Phorum ate my formatting!

Once again, with spots before my eyes:

life
.animal
..mammal
...primate
....human
..reptile
.vegetable

                             (etc) 

[Anon]

The formatting was lost in Phorum but funny enough, the email I got was correct. And even better I understood.

Thanx again Steve.

-Scott