Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Post Relational Database Design: Modelling product variants in an e-commerce application

    Hi,

    I'm working on the database design for an e-commerce application, and I'm finding it difficult to
    model the following situation:

    Each product may have 0 or more options, such as size and color, and each option may have 1 or more
    values, eg: Color: Red, White, Blue; Size: 5, 10, 15, 20, etc.

    Two separate products might have the same option/s, for example color, but with a different set of
    choices. eg: Nike Shirt (Colors: Red, Green, White) and Addidas Shorts (Colors: Blue, Green, Black)

    I would prefer the design to be in 3rd normal form, so something like this wouldn't really be optimal:

    Code:
    =========================================================================
    product_details:          | id          | name          | price         |
    =========================================================================
                              | 0001        | ABC Shirt     | 26.00         |
          
    =========================================================================
    product_options:          | product_id  | option_name   | option_values |
    =========================================================================
                              | 0001        | Color         | Red, White    |
                              | 0001        | Size          | 5, 10, 15     |
    More importantly, price may differ according to the size and color of the product.
    Not to mention the fact that each variant may have a different quantity in stock at any given time.

    So, essentially, a price and quantity has to be maintained for each product variant.

    Any ideas?

    TIA.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create table variants
    ( variantid integer not null primary key
    , sizeid integer null foreign key references sizes (id)
    , colourid integer null foreign key references colours (id)
    , fooid integer null foreign key references foos (id)
    )

    create table productvariants
    ( productid integer not null
    , variantid integer not null
    , primary key (productid, variantid)
    , foreign key (productid) references products (id)
    , foreign key (variantid) references variants (id)
    , price decimal(7,2) not null
    , quantity integer not null
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Rudy,

    I guess those aren't really repeating groups in the first table, but the problem is that each product may have a different set of options, and may not have color or size options at all.

    And if an option needs to be added, such as 'cover type (hard/soft)', a new column has to be added?

    Not an easy one this. Thanks for your help.


  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    somehow, i knew you were going to say something about adding another option

    okay, this means you have to normalise sizeid, colourid, fooid and any others to a many-to-many relationship with option types

    and a different set of options is easily accommodated, you have different combinations of rows in the intersection table

    no biggie

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

  5. #5
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Like this?

    [products]
    id, name

    [product_options]
    product_id, option_id, option_values

    [options]
    id, name

    Sample data:

    products:
    001, Shirt
    002, Shorts

    product_options:
    001, 002, (Red, Green, Blue)
    001, 001, (38, 40)

    options:
    001, Size
    002, Color
    003, Cover Type

    Problems:

    1. Option values violate 1NF.
    2. Quantity and price must be maintained for each combination of options.

    Thanks again.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. yes, that's a big problem, so don't do it that way

    2. but isn't that correct? i mean, shouldn't green size 10 shoes be a different price than blue size 9 shows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    2. Yes, they should each have a field for price and quantity.

    I came up with this today, but I'm still not really happy.

    http://ld.hostrocket.com/images/rdd.gif

    I'm soooo confused.

    TIA.
    Last edited by darkangel; 02-10-05 at 15:08.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks really good!

    tip: when doing examples, don't use 1,2,3 for every table (too confusing)

    use 1,2,3 for one table, 401,402,403 for another, 1156,1157,1158 for another, etc.

    as far as the option values are concerned, i'd probably have a one-to-many relationship between options and optionvalues

    yes, you might get 4 as the text description for two different values (e.g. dress size and shoe size) but they'd be separate options values with different keys

    without the one-to-many relationship, you could never generate a dropdown list of all the possible values that a user can choose form when selecting shoe size
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Thanks Rudy, I'll keep that in mind.

    I discovered a rather large problem though -- I need to store a quantity (and possibly price), for each combination of options.

    So for these options/option values: Colors(Red, Blue), Sizes(Small, Large), I would need the following:

    Red, Small -- qty., prc.
    Red, Large -- qty., prc.
    Blue, Small -- qty., prc.
    Blue, Large -- qty., prc.

    And the plot thickens...

    Thanks as always.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by darkangel
    I need to store a quantity (and possibly price), for each combination of options.
    this suggests, doesn't it, that each combination is unique, and that each combination should have its own identity (unique key!) and that the price and quantity are actually data attributes for the unique combination

    with me so far?

    so, what would be the unique key of each unique combination?

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

  11. #11
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    I'm afraid I'm not with you! I can't understand how this can be done (in a normalized manner).

    I'm trying! -- Really!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    each combination would have to have a foreign key for each option that went into it

    productoptioncombinations
    size FK
    colour FK
    covertype FK
    price
    qty

    the real monkey wrench in this scheme is that you can't use the combination of FKs as the primary key because PKs cannot have NULL in them anywhere, but not all options are mandatory (they are, yes, you guessed it, optional) and therefore must be declared NULL

    however, they should be declared in a composite UNIQUE constraint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Rudy,

    This is driving me crazy!

    A friend of mine came up with two designs. The second one is rather complex.

    Your thoughts?

    Design ONE:

    PRODUCTS
    Product_ID
    Product_Name

    INVENTORY
    Product_ID
    Option_ID
    Price
    Quantity

    OPTIONS
    Option_ID
    Param1_ID
    Param2_ID
    Param3_ID
    Param4_ID
    Param5_ID

    COLORS
    Color_ID
    Color_Name

    SIZES
    Size_ID
    Size_Name

    Design TWO:

    Image: Click Here
    Sample database: see attached.

    Is there no way of allowing for multiple options without altering the db structure?
    Attached Files Attached Files

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    second design is no good because it looks like product must have size and colour

    first design is not optimal because the following isn't 1NF --

    Param1_ID
    Param2_ID
    Param3_ID
    Param4_ID
    Param5_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    It seems that your Example 1 is close. I had a similar database design that required two layers of linking, and maybe this could work.

    Parameters
    Options ID
    Option
    Value

    Option links to a database containing records Color, Size, whatever else. Each record connects to the appropriate value table: Color 1:M with Colors (White, Black, Red, Green). Size 1:M with Sizes (Small, Medium, Large).

    Maybe that works?

Posting Permissions

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