CODEWITHSUNDEEP
mysqldatabase-designdata-modeling
Andrew
Andrew

Reputation: 2154

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 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?

Upvotes: 1

Views: 643

Answers (1)

Marius Burz
Marius Burz

Reputation: 4645

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.

Upvotes: 6

Related Questions