Shopping Cart ordering tables

Question

I am developing online Shopping Cart system. Now I need to work on adding the items/basket into order tables. I wanted to make sure if I'm going on the right path?

These are the main products tables:

items table

• item_id (p)
• item_name
• item_description

Note: item prices are in the item_options tables

item_options table

• option_id (p)
• item_id (f)
• option_name
• option_price

item_extras table

• extras_id (p)
• option_id (f)
• extras_name
• extras_price

Below are the orders tables. As you can see the item name, option name and extras name, prices are copied into orders tables. If any of item name or prices changed - it will not affect the order tables. For example the invoices will not be affected.

orders table

• orders_id (p)
• customer_id (f)
• address_book_id (f) (for delivery adddress)
• date_purchased
• orders_status
• orders_date_finished

order_items table

• orders_items_id (p)
• orders_id (f)
• item_id (f)
• item_name (copied from items table)
• item_description (copied from items table)
• option_id (f)
• option_name (copied from item_options table)
• option_price (copied from item_options table)

order_extras table

• order_extras_id (p)
• orders_id (f)
• orders_items_id (f)
• extras_id (f)
• extra_name
• extra_price

Answer 1

I'd like to raise two potential optimisations to this database schema.

Firstly, are there any extras which could apply to two different options e.g. options and extras has a many-to-many relationship? If so you need to add a link table with the following structure:

 optionExtras
 optionId, pk
 extraId, pk

you then alter the extras table by removing the optionId field. This could remove potential redundancy.

Secondly, instead of duplicating item names, option names and extra names why not add a boolean(0-1) row into each table titled active which signifies which entries in each table are currently possible to be selected at the time of ordering. This way the extra redundancy is avoided and products can be re-instated, without any effect on information on old invoices. When you want to change the price or name of a product you just turn the current active record to off and then insert a new record with the new information. The old orders obviously still point the correct id of the entry that is not active.

You could also take this one step further and use the active column to signify products which are available to people that type in special codes e.g. label these products active = 2.

Anyway, those are my thoughts, hope they are helpful!