Database schema for shopping cart

Databases
1

Hi, I am trying to set out some shopping cart tables where prices may on some products relate to all sizes and colours and for others, the price may be specific to size or color (or other product-specific value)

I may have confused my plan, in another thread, with my eg values.

necessary to clarify: the attribute name column would store just one value in all rows. that value might be color or size or description or unit of measurement (as examples).

Then, in the attribute_values column, it would store one value, which relates 1:1 with the attrubute name col.

And the unit of measuremnt would relate 1:1 with the attribute value col. (or it would be null if no unit of measurement applied).

Having shown columns for relating the price to an overall product eg all shirts whatever their colour, where should I store the price when it would be size dependant?



create table products
( id int not null auto_increment primary key
, product_number varchar(24) NULL
, product_name varchar (99) not null
) engine=innodb etc

create table product_prices
( id int not null
, product_id int not null
, price decimal(6,2)
, currency_code char(3)
) engine=innodb etc

create table product_attributes
( id int not null
, product_id int not null
, attribute_name varchar(99) eg colour/size/weight
, attribute_value varchar(24) not null eg red/44/250
, attribute_unit_of_measurement varchar(6) NULL eg kg/quart/gall/ml
) engine=innodb etc

At the risk of this being an EAV model and to be avoided unless necessary, what other way should I structure my tables?

The only other way I have managed to get my head around is (I think), unnecessarily table heavy.
tables each for:
products
| id | product_name |
product_colours
| id | product_id | colour |
product_sizes
| id | product_id | size | unit_of_measurement |
product_weights
| id | product_id | weight | unit_of_measurement |
product_descriptions
| id | product_id | desc | short_long |
product_images
| id | product_id | image |
product_prices
| id | product_id | price |

But then how should I relate a price to a product such as shampoo, where different sizes are different prices? and if price was size dependant and colour-dependant, would I need a m2m table for each such variation?

I can’t help but think I am over thinking this somewhat so I need some help please.

bazz

2

This is the pattern I used when I last worked on a eCommerce system that supported varying attributes across line of products that change in price. While it is relatively complex it is very flexible and generic enough to be used to support most types of products. In short the you need to think about a “product” as being a container. Then what I term “part numbers” are the physical items. A product (container) can have multiple attributes and each attribute can have multiple option values. The option values are than associated with a part number via a standard m:n relationship with a look-up table.

3

I knew I had written a nice example somewhere of the pattern and test data.

Product Schema Example

4

Thank you very much oddz.

Some of my products will have the price related to the variations eg sizes of shampoo. others will have a standard price for all variations eg shoes.

So would you recommend that price should be stored in the part_numbers_to_products_options_values table OR should it be an FK to a prices table?

I am just struggling to get my head around the best approach to make price storage consistent between products that have a standard price across all size/color variations (eg shoes), and those which are specific to a size (eg shampoo).

bazz