Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2015

    Unanswered: Handling stocks for product variants

    In a custom ecommerce web site, I need to handle products, product options and product variants. I need all of them to be dynamic. So admin should be able to create products, product options and assign product options to product variants. I have designed following db schema for this requirement. I want to focus on product management flexibility as well as performance here.

    products: id, name, description, qty, base_price, parent_id...

    options id, name

    option_values id, name, option_id

    product_options product_id, option_id

    product_option_values product_id, option_id, option_value_id, qty, price

    DB design

    The problem is that I cannot store quantity or price for a product like Red XL T shirt. I mean a product with multiple options. Can anyone help me solve this?
    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    The problem lies in how optional are your options. In other words do all shirts have the same color options, or do different shirts have different choices (or no choice) of color? Do all shirts come in all sizes, or are some only offered in one size (or one size fits all)?

    Since most inventories require a continuous variation of colors, sizes, etc which vary by product let's consider that case first. The way that I'd handle this problem is to use a many-to-many linking table. This allows one shirt to have no options at all, but other shirts to have a size and a color. Depending on the choices that you make it can be challenging to only allow one color or size per item and that limitation may or may not be appropriate.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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