#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    390
    Rep Power
    13

    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
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    390
    Rep Power
    13

    items


    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 ?
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Posts
    390
    Rep Power
    13

    total


    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"

IMN logo majestic logo threadwatch logo seochat tools logo