building a database for my site

Question

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 customizations 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...

Answer 1

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.