Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: 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.

    TIA

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    TIA,

    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    9
    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?

  4. #4
    Join Date
    Feb 2004
    Posts
    42
    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?
    Last edited by FastCougar; 10-27-04 at 15:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •