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.
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.
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?