var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Relational Database Design: Modelling product variants in an e-commerce application
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.
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 |
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.
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
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.
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
product_id, option_id, option_values
001, 002, (Red, Green, Blue)
001, 001, (38, 40)
003, Cover Type
1. Option values violate 1NF.
2. Quantity and price must be maintained for each combination of options.
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.
I'm soooo confused.
Last edited by darkangel; 02-10-05 at
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.
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
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
This is driving me crazy!
A friend of mine came up with two designs. The second one is rather complex.
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 --
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.
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?