building a database for my site

I need help designing the database for my online custom suit store. So far, i have created the tables for the products, orders, and my users/customers. I think I need to add some tables to manage my shopping cart for customers while they are making their selections and customizing them. At this point, i am a little confused on how to proceed. does my shopping cart need to pretty much hold all the information that my orders tables will hold ultimately? as you can see from the attached image, orders have order items, and order items have order item options. this way, i can pull up each order item that a customer wants, and all of the customiz开发者_如何学JAVAations that go with each particular order. Does this need to first done in my shopping cart, meaning that i would basically have to replicate all of the tables in the orders section of the database, or is there a better way? Also, what else is my database application lacking in order to robustly run a website like www.indochino.com?

All help is appreciated...

You should change your order table to be a shopping_cart table, and remove all of the shipping address information from it.

Then create a separate order table that includes a shopping_cart_id that points to the associated shopping cart. This order table should include all of the necessary information for a posted order, like shipping address, line-item total amount, tax amount, order total amount, posted-date-time, etc.

I don't think a status field is the correct approach, because a shopping cart is not an order, and lots of order fields are not applicable to a shopping cart. These are fields that should not take a null value, because they are required for a completed order. Tables that store completely different things depending on a status field are a burden when it comes time to write reports on this data.

I don't think you need a separate table for a shopping cart. I would just use the existing order/order_item tables to hold the shopping cart information, and then add a status field on order that held values such as "incomplete", "complete", "shipped", etc. Any orders that are incomplete are rendered as the shopping cart. A state machine would be good candidate for managing the lifecycle of the order, IMHO.

Assuming your shopping cart is a separate table, it should have orderid and userid as foreign keys.

Would it work if you put an "in_cart bit" column on the order table? That way you can use the same tables but set in_cart to 1 where the order is not finalized yet.