Database design - ordering without signing up

Ok lets say im building a online shopping site. For the database I have the following tables (in addition to others)

orders

CUSTOMER
shopper_id
f_name
l_name
email
password
register_date
modify_date
shipto_street
shipto_city
shipto_state
shipto_zip
billto_street
billto_city
billto_state
billto_zip

ORDER
order_id
shopper_id
total
status
date_entered
date_changed

ORDER_ITEMS
order_id
sku
quantity
adjusted_price


my question then becomes, how do you handle orders for anoynomous users ? if your going to allow customers to buy without first registering (as is common now with many ecommerce sites), then you need some kind of anyonomous guest user account and maybe store at least the shipping info in the orders table


comments/suggestions appreciated

You're thinking about this the wrong way. The cart is not the same as the order. You need a seperate table then those listed to hold the cart UNTIL the order is placed. This can be related to the annonymous user by a random identifier (such as an MD5 hash). When the person places the order and you've gathered their personal information including billing data, then you move the cart to the orders table.

thanks for the help, futher questions if i may:

ok, so how to store items in the basket ? im thinking of having an almost identialca set of tables for the basket as the orders - in otherwords, since i store order items in a seperate table from the order itself, the basket would need a similar structure , correct ?

Right. In addition to:

ORDER
order_id
shopper_id
total
status
date_entered
date_changed

ORDER_ITEMS
order_id
sku
quantity
adjusted_price

I'd have

CART
cart_id
date_entered
date_changed

CART_ITEMS
cart_id
sku
quantity
adjusted_price

I wouldn't put a total in ORDER or CART. That's a function of the total from CART_ITEMS and therefore redundent and an opportunity for corruption/discrepency.

yep, thanks for the tip on total, that makes sense

BTW - im using a database aproach to building a shopping cart versus using code. In the past ive used ASP and session objects and recently ive used JSP and javabeans to build a cart. I want to use a set of database tables and index into the tables with a cookie, versus having a bunch of objects in memory


also - im going to be selling hard to find "Confederate wearables"