Category + Subcategory help

Hi,

I'm currently working on coding my own directory and came across a little problem.

I already have a mini directory on another site I run for the purposes of link exchanges with that site. This directory has link categories and in each are subcategories. The directory is here: http://www.coffeesh0p.com/Links/ - My links database comprises of 3 tables: links, categories and subcategories. Each category has an id and a title, each subcategory has an id, it's respective category id and it's title, while the links table has fields for link information, and id and a subcategory id. So if I select a category from the database, I can select all subcategories with that category id and all links in each of those subcategories by the link's subcategory id.

Now this new directory I'm working on needs to be a bit more advanced than that. I noticed on Aviva that some subcategories have their own subcategories EG http://www.avivadirectory.com/Arts-and-Literature/Performing-Arts/Dance/Folk/

This is something I want to impliment, but I have no idea how to go about it.

Could anyone offer any advice, or perhaps the database schema from your own multidimensional directories?

Thanks in advance for your help.

 

You probably will want to work off of parent and child ids.

 

Hey,

It is not too complicated, first off you would use just one table for simplicity, the table would look somewhat like this (plus additions for meta etc):

CREATE TABLE Cats (
CategoryID int(11) NOT NULL PRIMARY KEY,
ParentID int(11) NOT NULL DEFAULT '0',
Category varchar(255) NOT NULL
);

Code (markup):

Ok, now the way it works is that all top level categories are assigned a ParentID of 0 (the default). So lets say we have:

1 - 0 - Arts
2 - 0 - Sports

All Sports sub categories would then be assigned the primary key of Sports as the ParentID, like so:

3 - 2 - Football
4 - 2 - Tennis

All sub categories of Football would then be assigned the primary key of Football, like so:

5 - 3 - World Cup
6 - 3 - European Cup

And so on. This allows for an unlimited category tree, providing the front end is coded correctly to use the data

HTH

 

For simplicity's sake, what silky said is the best way to go about it.

 

This makes sense to me, so thanks.

Is this how big directories do it, like PHPLD?