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:

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.

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
)

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.

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

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.

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?

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.

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

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.

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?

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!

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

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?

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

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?