Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Question Design question, Category, subcategory and product

    Hello,

    I'm trying to set up a database for a website and need some help as I am starting to bang my head against the wall. Here is the situation:

    There are 5 categories, each has between 5-10 subcategories

    There are infinite amount of products.

    Below are some of the options I have come up with. Which is the best and why? (Also Is there any difference between the first 2)

    -------------------------------------

    1. Categories
      category_id (PK)
      category
      category_description

      Subcategories
      category_fk
      subcategory_id (PK)
      subcategory
      subcategory_description

      Products
      category_fk
      subcategory_fk
      product_id (PK)
      product_description

    2. Categories
      category_id (PK)
      category
      category_description

      Subcategories
      category_id(PK)
      subcategory_id (PK)
      subcategory
      subcategory_description

      Products
      category_id(PK)
      subcategory_id(PK)
      product_id (PK)
      product_description

    3. Categories
      category_id (PK)
      category
      category_description

      Subcategories
      subcategory_id (PK)
      subcategory
      subcategory_description

      crosswalk table
      category_subcategory_id (PK)
      category_id
      subcategory_id

      Products
      product_id (PK)
      category_subcategory_id
      product_description



      Also I would like the option to place a product under multiple category/subcategory combinations, if it is not too complicated.

    4. Categories
      category_id (PK)
      category
      category_description

      Subcategories
      subcategory_id (PK)
      subcategory
      subcategory_description

      crosswalk table
      category_subcategory_id (PK)
      category_id
      subcategory_id

      Products
      product_id (PK)
      product_description

      product_category_subcategory
      product_id (PK)
      category_subcategory_id
      category_subcategory_id2
      category_subcategory_id3



    Any help would be greatly appreciated. Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you need subcategories I'd suggest you change you categories table
    categories
    CategoryID 'autonumber
    Description
    ParentCategoryID 'nullable

    the parent category is optional if its not defined, then defacto that category is a top level 'master' category.

    if you need a product to have mulitle categories then youneed and intersection table

    productcategories
    ProductID 'PK
    categoryID 'PK
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this article may help -- Categories and Subcategories

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2007
    Posts
    15
    Thanks to both of you for your help. The link had the info I was looking for.
    It's actually what I had originally planned to have all in one table. But then I figured it wasn't normalized unless categories and subcategories were in their own table.

    The querying of the table is a bit complex. However, I think I will only go 2 deep category --> subcategory so hopefully not too bad.

    The article had the queries to generate a menu as well as breadcrumbs. good stuff.

    I'm working on the design now. When I get up to writing the queries I may be back .

    thanks

Posting Permissions

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