Modeling Items and Options for shopping cart

I am working on a shopping cart application, and I am pretty much stumped on how to model Items and Options according to the following requirements:

1. Each Item may have zero or more Options (color, size, etc)
2. Each Option may 开发者_C百科have several different values (e.g. green, blue, red, and orange for color)
3. Two Items with the same Option may have different values for that option (e.g. you may order a t-shirt in green or orange, and you may order a ball cap in blue or red)

I'm sure that this is a somewhat common scenario, but it is not one that I have ever faced before. Any ideas?

---

item table (contains the items)

item_id
name

---

options table (contains all options)

option_id
name
type -- color, front_color, back_color, size, shoe_size etc.

---

option_value table (stores all available values per option)

option_value_id
option_id
value

---

item_available_option (stores all available options per item)

item_id
option_id

---

item_available_option_value (stores all available option values per item per option)

item_id
option_id -- not required, but I added since it's easier to figure it out
option_value_id

---

orders table (stores the orders)

order_id
customer_id
order_date
billing_address
delivery_address

---

order_position table (contains the order positions)

order_pos_id
order_id
item_id
quantity

---

order_pos_option table (contains the options for each order position)

order_pos_id
option_id
option_value_id

This is quite a generic approach that allows for an undefined number of options and option value that are defined on a per item basis.

If there are not that many options, an alternative would be to go specific, something like this:

---

colors table (contains all colors)

color_id
name

---

item_available_colors (available colors per item_id)

item_id
color_id

---

sizes table (contains all sizes)

size_id
name

---

item_available_sizes (available sizes per item_id)

item_id
size_id

---

order_position table (contains the order positions)

order_pos_id
order_id
item_id
quantity
color_id
size_id

The item and orders tables stay the same, all others are not needed anymore.

There are lots of other possible variations, this is meant to provide you with a starting point.