Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Database design for product catalog site

    I am working on an e-commerce store consisting of products and categories. I have 3 main ways of filtering products: by category, by manufacturer and by gender.

    I have the following tables

    products
    - id
    - name
    - manufacturer_id
    - gender_id

    categories
    - id
    - name
    - parent_id

    products2categories
    - id
    - product_id
    - category_id

    manufacturers
    - id
    - name

    gender
    - id
    - name

    Using these tables I can list products e.g. find all products where manufacturer_id = 1, gender = 1, category= 453.

    However I want to have the control of setting images, page titles, meta tags, display order etc for each of these filter combinations. So I have added another table:

    pages
    - id
    - category_id
    - manufacturer_id
    - gender_id
    - image
    - page_title
    - meta_description
    - display_order

    This works, but I now have the task of making sure every single possible combination is in this table. For example when adding a new product, I will need to create all the combinations. Also if I want to add more filters in the future, the table is going to get bigger and bigger. Is there another approach I could use, or is this the only way?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you mean that you might want to display:
    image X for manufacturer_id = 1, gender = 1, category= 453,
    image Y for manufacturer_id = 1, gender = 2, category= 453,
    image Z for manufacturer_id = 1, gender = 1, category= 454,
    and so on...

    then you have no choice but to add a table keyed on (manufacturer_id, gender, category) where you can assign the images and other attributes.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Is there another approach I could use, or is this the only way?
    I can understand having an image associated with the category but why would you want separate images for each combination of gender, category and product? How would it benefit the user?

    If you're determined to go on this way then couldn't you just have an image associated with each category and another with each manufacturer and then just display both images next to each other. If the images had transparent backgrounds then you could just have a blue or pink (am I being stereo typical?) background displayed at run time.

    This would be far less effort to maintain and would probably be easier to understand by the user rather than expecting them to decipher whatever the differences are between image 1234 and image 1235.

Posting Permissions

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