A Subject Often overlooked in e-Commerce Databases.
Barry-

I love your site and have reviewed all that it offers.  Unfortunately my problem is still not addressed.

Simple problem with no good solution.
Basically should one create a shopping cart model / table as well as an order model / table when designing an ecommerce application.

For example, in the real world when a product is selected it is essentially added to your cart.
When you place the order the items are taken OUT of your cart and and order is created.


To represent this in a db structure you might have:

CART
CART_LINE_ITEM (cart_id, product_id, price, quantity, etc)
ORDER
ORDER_LINE_ITEM


Now my issue with the above is redundancy and duplication of records.
For example, if a coupon is used then its stored in the cart since you technically have "coupons in your cart."
 Then when the order is placed it is only logical to "apply that coupon," which means moving the coupon over to
the order table.

Worse IMO is that each line item is then literally duplicated from the cart to order line item tables.


Of course, this entire issue can be rectified with this:
CART
LINE_ITEM (cart_id, etc)
ORDER (cart_id)


In that scenario you are basically "buying the cart."

This seems ok but I have a weird problem with it.

My issue is that, over time, I will probably want to kill old carts after rolling up the data into a reporting table.
With this structure that becomes impossible since killing the cart would destroy line items and therefore the order would be incomplete.

Spree, a popular Rails ecommerce Open Source application, handles this differently (which I am currently following).

They have this:
ORDER
LINE_ITEM

NO cart!  So the minute you add something to your "cart" you are basically creating an order.
You end up with an ORDER table filled with both placed and abandoned orders.


I *think* its ok but it just feels "unclean."
Logically you shouldnt really create an order until, well, you order something.
Its also weird from a roll-up perspective as I would be "purging" the order table of all abandoned orders over time.

That scares me.

I just don't like mass deleting items from an orders table (ugh).


So Im stuck.

I feel like the best scenario is the first since it separates the cart entirely from the order.

If I want to delete the carts, no problem, don't touch orders.

The only issue would be if I store the cart_id with the order so I can reference back.

Of course if I copy things from cart to order then I really don't need to reference them.


With whatever solution I pick I need to be able to answer these:

What does my conversion rate look like from visit to cart, visitor to order, cart to order?
What % of my carts are abandoned?
What are the conversion rates on a per product basis?
What is the abandonment rate per product?

By separating the models its REAL easy to say "give me the total visitors, carts created and orders placed for this time period" and calculate.

I can also get those with a flag or status column that marks "in process or complete" with the single ORDER (no cart) model.

What do you think?

You think its better to have a single ORDER table with a "completed?" flag and calculate everything from there
or should I do the 3 or 4 table solution (or something else?)

Thanks and Happy Holidays!

Josh

B. The Things of Interest, ('THINGS'), include :-
B.1 Items
B.2 Orders
B.3 Products
B.4 Shopping Carts
B.5 Others to be determined.

C. These THINGS are Related as follows :-
C.1 To be determined.

D. Other Characteristics of these THINGS include :-
D.1 To be determined.

E. Sample Data includes :-
E.1 To be determined.

F. Typical Enquiries include :-
F.1