How to add sub-categories??

Hi all,

Can any body tell me how to add sub-categories to a database (MS SQL Server 2000) please.

I have a db with the following tables set:
Products: ProductID, CategoryID, ProductName
Categories: CategoryID, CategoryName

If a sub-category needs to be added how would I add it and what fields would it contain and the relationship between the Products and Category Table be?
Also any useful tips on this would be appreciated.

TIATIA,

If the sub-categories are the same as the categories you can just add a SubCategoryID field to your Products table. Then create a relationship between Category.CategoryID and Products.SubCategoryID.

Have some fun.|||Then does that mean i would have to add a Sub-Category Table... and the db would be like this:

Products: ProductsID, CategoryID, SubCategoryID...etc
Category: CatogeryID...etc
SubCategory: SubCategoryID...etc

the category table would have a 1-many relationship with products and a 1-many with sub-categories too?

For instance ... the products table would have books, games, music ...etc as products and books would have computers, history, science as it's sub-cats. games would have ps2, xbox ...etc as it's sub-cats.

So what would be the best way to describe the tabels and there relationships?|||I have found the best way to handle this is to have one table with an added field called 'parentID' or something like that. This value is the key on the row of the 'parent' category. This way, you can have unlimited subcategories.

Products: ProductsID, CategoryID, ...etc
Category: CatogeryID, ParentID, CategoryName, ...etc

Category Table (|seperator)
1 | 1 | 'Food & Clothing'
2 | 1 | 'Clothing'
3 | 2 | 'Shirts'
4 | 2 | 'Pants'
5 | 3 | 'Crew Neck T-Shirts'

Here we see that 'Crew Neck T-Shirts' is a sub category of 'Shirts', which is a sub category of 'Clothing', which is a sub category of 'Food & Clothing'. As you can see, this way allows for unlimited nesting of categories and is the best solution because a sub-category table would have the exact same information as the category table, so why duplicate the effort and over complicate the database?