Database design question on E-Commerce application...

Question

Hello guys... I´m designing an ecommerce database... I´d like suggestion to design the following case :

• A product may have several sizes and several colors
• Each combination of products´s size/color must have a specific price

So, I can have a Product X with sizes: A,B,C and colors: Green,Black and White

And each combination have its price...

It´s implemented in Amazon, one example here : http://www.amazon.com/Champion-Mens-Jersey-Black-Large/dp/B0010EEYCU/ref=sr_1_4?ie=UTF8&s=sporting-goods&qid=1287767784&sr=1-4

I´d like help in designing that !

EDIT: A Product can have a price without Size nor Color

Thanks

Answer 1

Among the many ways of approaching this, I'd highlight these:

1. Have a separate Product record for every colour/size combination of every item. Each has its own price.
2. If many items will share similar sizes and colours (if you're selling shirts, for example, and every shirt is available in any of five sizes and three colours), it may make sense to normalize Size and Colour into their own tables, and have a PricePoint table that stitches them all together - every combination of item, colour, and size has an entry in PricePoint with the appropriate price.

Note that the second approach, while more-normalized, could cause issues in product / inventory tracking. Since it's easier to track individual items if each item at each price point has its own SKU (or other common identifier), you may prefer a mixed approach:

• A Product table that will contain a record for every combination of item, size, and colour, with a unique SKU and Price for each.
• A Size table that normalizes out the common sizes your Products may have. Product has a foreign key to this table.
• A Colour table, as above.
• A ProductType or ProductGrouping table that defines supersets of products for ease of organization / search. Each Product will have a foreign key to this "parent product" table. For example, you might have a ProductType = 'T-Shirt' that has several dozen Products associated with it - one Product for each combination of shirt style, size, and colour.

Update: To elaborate on the "superset" table, per the OP's request, I would extend @Phil Sandler's example this way:

Add a Product Group table:

Product Group (defines a superset of similar products that will be grouped or filtered together)
product_group_id
product_group_name

And edit the Product table to add a foreign key to Product Group:

Product Table (defines the product):
product_id
product_group_id
product_name

Now, to highlight an example combination at a particular price point, some made-up data:

Product: product_id=100, product_group_id=1, product_name='Men's Crew-neck T-shirt'

Product Group: product_group_id=1, product_group_name='T-Shirts'

Color: product_color_id=10, product_id=100, color_id=6 Assume '6' is 'Blue'. This record means the Crew-neck T is available in Blue

Size: product_size_id=11, product_id=100, size_id=2 Assume '2' is 'Medium'. This record means the Crew-neck T is available in Medium

Price: product_price_id=555, product_id=100, product_size_id=11, product_color_id=10, price=24.99 *This record means the Medium Blue Crew-neck T is priced at $24.99 (note the price_id, which was missing from Phil's example)*

The Product Group table would, using this example, let you do queries across a product line, such as "Select the most-expensive Large T-Shirt that we sell."

Answer 2

One possible design:

Product Table (defines the product):
product_id
product_name

Product Size Table (defined valid sizes for the product):
product_size_id
product_id
size_id (assuming here that you have a lookup table for generic sizes like S/M/L/XL)

Product Color Table (defines valid colors for the product):
product_color_id
product_id
color_id (again, assuming there is a lookup for Blue/Green/Purple/etc.)

Product Price Table (applies a price to the product/size/color combination):
product_price_id
product_size_id
product_color_id
price

Answer 3

The standard approach here is to consider product and SKU as separate things. A SKU has a specific size, colour, and price, and a product is a family of related SKUs.

create table product (
  product_id integer primary key,
  name varchar(255) not null,
  description varchar(2048) not null
);

create table sku (
  sku_id integer primary key,
  product_id integer references product,
  size varchar(40),
  colour varchar(40),
  price numeric(8, 2) not null,
);

You can normalise colour and size by making the fields in SKU references to rows in colour and size tables, rather than freeform text. You can also separate out pricing, so you can do things like prices at different dates, discounted prices, and so on.

You typically build your site around the products, and display the range of SKUs as just one of the details, in a table or dropdown or something somewhere on the product page.

I'm not saying this is the best way of doing things, but it seems to be standard in the ecommerce sites i've worked on.