SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table design for products and their options

    Hi there

    I'm in the process of developing an e-commerce site. I've done plenty before but never with a feature that allows admin to add product options. I developed product attribute system, but I'm not happy with it so I'm starting again....

    In terms of how the product options should be presented at the frontend, I would like it to look something this:

    http://www.chainreactioncycles.com/M...?ModelID=15892

    As you can see, it has all available variations of the product listed to the right.

    How would you suggest the backend be designed for this?

    Would it be a case of having a products table which stores the information that is applicable to all options such as name, description and image; and then a product options table which will store all possible variations of the that product even if there is only 1 option?

    What do you think would be best practice so that I have something easy to manage and code and that will allow me to display the options as per the link above?

    Many thanks

  2. #2
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've looked at this a little more and I'm thinking I might be best keeping it simple and just having a products table to store the main product info and a options table which contains all variations eg:

    PRODUCTS
    prod_id | brand_id | default_photo_id | prod_name | prod_desc | prod_added
    ---------------------------------------------------------------------------
    1 | 1 | 1 | Bobby Dazzler T-Shirt | Vibrant T-Shirt to make you stand out in the crowed | 2009-02-25 12:15:07

    PRODUCTS_OPTIONS
    option_id | prod_id | option_desc | price | sale_price | in_stock
    --------------------------------------------------------------
    1 | 1 | Small, Green | 20.00 | 15.00 | 1
    2 | 1 | Medium, Green | 20.00 | 0.00 | 0
    3 | 1 | Large, Green | 20.00 | 0.00 | 1
    4 | 1 | Small, Red | 20.00 | 15.00 | 1
    5 | 1 | Medium, Red| 20.00 | 0.00 | 1
    6 | 1 | Large, Red| 20.00 | 0.00 | 0

    Here is another example site that lists all options with buy buttons:
    http://www.wiggle.co.uk/p/Cycle/7/En...ts/5360029511/

    I did develop something whereby you have an options table, options_value table and product_attributes table. That was scalable and more normalised but it was more difficult to manage and much more difficult to offer different prices for an option and option value combination.

    Any thoughts much appreciated.

    Many thanks

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    next time you're in a bricks and mortar store where products with different options are on sale, have a look at the bar codes

    they will reveal that different options = different product

    your problem goes away completely if you simply register each different set of options as a distinct product, yes?

    the only problem people have with this concept is that they feel there will be "too much redundancy" in the data

    what are your thoughts? what would you be unable to do if you took this approach?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Many thanks for your reply.

    I did think about this but wondered if there would be far to many rows without data such as a description. In most cases there wouldn't be photos associated with the options as most options won't require a photo(s) different from the parent product.

    I guess I could add a 'parent_product_id' column in the products table which would identify a record as an option. But I'm wondering if storing the options in a separately table as shown in my 2nd post would be somewhat neater.

    In answer to your question, there probably isn't anything I couldn't do by having the options as entry in the product table. At least I can't think of anything that I would want to do.

    Perhaps having options in a separate table makes designing a user friendly admin interface easier and more logical.

    What do you think? I guess I need reassurance to follow a path that I won't later regret. My initial route of having options, options_values and product_attributes (relates products to options and options_values) was far more complex, but provided a neat solution at the frontend, but also quite restrictive. I'm just hoping this new approach isn't too 'crude'.

    Thanks

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sxtrail View Post
    I did think about this but wondered if there would be far to many rows without data such as a description. In most cases there wouldn't be photos associated with the options as most options won't require a photo(s) different from the parent product.
    there are two approaches

    1. make sure every row has a description and photo

    2. come up with a "parent product" table (often this is called the product brand)

    the problem with 1. is that most techies see the redundant data and immediately want to optimize it

    disk space is probably ~the~ cheapest resource item in the entire mix (compared with query complexity, cpu time, programmer time to develop complex solutions, etc.)

    the problem with 2. is that you will have two rows in two tables for any product which has a single occurrence


    i guess the choice might depend on how many products there are, how many options each product has, and the proportion of products that have multiple options versus products that have only one option...

    .. as well as your comfort level with coding complex solutions to simple problems

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    I like your thinking. I usually pride myself in providing solutions that are simplistic and user friendly, but that provide all the features that are required and avoid unnecessary fancy pants stuff that never gets used.

    That is one reason why I immediately dropped my first implementation.

    So I'm now stuck between my 2 remaining options and kind of leaning towards the 2 table solution but I hear you when you talk about those products which only have one option. At the same time, I really can't see options having their own description aside from the option description such as 'Large Blue Loose fit'.

    This does need to be scalable and relevant to different types of shop. The first implementation is for a clothes shop, so there are likely to always be multiple options thus making the 2 table solution more appropriate.

    There is one more option here.... Have price, sale price etc also in the products table so that the first option is stored in the products table. Therefore, if no additional options then no entry in the options table for that product.

    Like you say, it should be simple.... but powerful and capable at the same time.

    Many thanks

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    Just a quick message to let you know that I opted for the solution you suggested I now store all product options in the product table and just have a parent_prod_id column to identify products as options to another product.

    It works well and the knock on effect is that the table structure for 'baskets' and 'orders' is much simpler and therefore my coding is easier

    I have a few redundant columns but the significant decrease in tables and query length more than makes up for it. I can't imagine this system ever storing more than 3000 products which is nothing really. Also, the redundant columns are all small numeric types except the description which is of type TEXT.

    Thanks again

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're welcome, and thanks for the update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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