SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Feb 2008
    0 Post(s)
    0 Thread(s)

    Shop theory help

    Hay, I'm in the process of building a custom online shop and have run to a bit of a problem. I'm not sure how many of you have made shops before but here's my problem

    I have a product, this product comes in 5 different sizes and 3 different colours. Each colour has 5 sizes.

    so we have:

    red 1, red 2, red 3, red 4, red 5
    green 1, green 2, green 3, green 4, green 5
    blue 1, blue 2, blue 3, blue 4, blue 5

    Now from what seems to be 1 product i now have 15 products!

    How do you guys come across this problem?

    Do you store each product inside a table and do some clever SQL to link all product 'variations' into 1 product?

    like this:

    variation_product_id = 4 etc

    Or do you store each 'variation' of the product inside a column?

    like this:

    size = '1,2,3,4,5,6'
    color = 'red,green,blue' etc

    If that is the case how to keep count on stock levels?

    I hope i make sense as many of you know i suck at English.


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    15 different products | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2008
    Gloucestershire, UK
    0 Post(s)
    0 Thread(s)
    If every product had the same choice of size/colour etc. you could create a general products table:

    product_id | name | description | price (and the rest)

    A colours table:

    colour_id | colour

    A size table:

    size_id | size

    Specific product table:

    spec_id | product_id | colour_id | size_id | (and barcode/SKU)

    So if you create a product in the CMS, you'd add the usual details and select colours/sizes. The usual details would be entered into the General Products table, and you would then have to capture the id to a variable (mysql_insert_id() if you're using PHP/MySQL), then create a new query where you would enter the product_id, colour_id, size_id to the specific product table. That structure would also make it much more flexible for displaying products in the front end.

    I hope that makes some sense.




Posting Permissions

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