I'm going to start work on a new version of a news script I'm using when making websites for my clients, and I'd like to remedy its biggest flaw: there's only one level of news categories.

What I want is a dynamic category system where I'll be able to create an unlimited ammount of sub categories, but I don't know where to start looking to learn how to design the database for this.

I did some searching on these forums but the posts about "nested" and "adjacency" models whent right over my head. Maybe someone could provide a link to an article which explains this in a more concrete way.

Thanks in advance for any and all information you can give me!

You're looking at creating a recursive relationship.

Here are articles on the topic

http://searchdatabase.techtarget.com...285649,00.html

http://www.utexas.edu/its/windows/da...dm/schema.html

http://www.slu.edu/colleges/business...ECURSIVEER.DOC

hey certus, thanks for mentioning one of my articles

here's another that shows some of the queries that you might use against the category hierarchy -- Top N subcategories

be sure to go to Part 2 of the article to see my "order by coalesce(parentid,id)" trick


rudy

Anytime, rudy.

Great! Thanks for the links, I'll see if I can get my head around them.

I really like the method described by Rudy in this article (the COALESCE trick). But this method seems so much simpler than doing the whole "modified preorder tree traversal" thing so I'm thinking there must be some negative sides to using it. Am I paranoid?