Smart way of handling this

Anon

Ok, I'm making an article engine using PHP/MySQL. It's going pretty well, I just have one question regarding how I should handle article categories.
This is my categories table:
table article_categories(
categoryID tinyint(2) NOT NULL,
categoryName char(255) NOT NULL,
PRIMARY KEY (categoryID),
UNIQUE ID (categoryID)
);
Now I want to have subcategories as well under categories, so I could have a category called programming and then subcategories PHP, Perl, etc.
What would be a smart, optimized way of doing this?

Anon

Ok, maybe my categories table is bad too?
Is there some other way I can make it?
Please, help.

Anon

I am not sure if this is the smartest way, but i think it would work

I think you should have a parent column in your categories table.

This will allow you to have a tree based structure that should be fairly easily navigable.

Selecting based on parentID = 0 would give you the top level categories.

To go to the next level of subcategories, select parentID = the current category. Continuing this would allow you to move down through the sub-sub ... sub categories.

I hope this is of some use.

Anon

Hello,
I think you could use 2 tables, one keeping track of each category's details, and another one for relationships between categories (parent, sun, parent to parent =>grandparent).
Then, at runtime, if you delete an intermediare category, you don't have to "move" the entire record from first table, just modify the relationships.
This way you'll have separate properties/relationships.
Of course, the best way of designing it would be knowing first what's this structure intended to do.

Calin

Anon

The best way would be to have an article_subcategory table with primary key, foreign key to article_category and categoryName fields. You will definatly need to change your tinyint to a real int for safety reasons.

To stick with one table, you will need:
id int primary key
parent_id int foreign key to id
name description

so all with parent_id is null or = 0 are top level and select where parent_id = cat.id would give you the next level.