Shopping Cart table issue

[Amit Kela]

Hi,
I am using ASP on my webpage to pull up data from SQL tables. I am
using a Shopping cart page where the users can place orders.
The problem I have once the user has logged in and placed an order
the shopping cart table is empty (as it should be).
But when the user tries to place another order in the same session by
adding an item to the cart, the table shows the new item along with
the old item that has already been ordered (instead of just the new
item)
The SQL table does flush the old data after the order is placed and
the table is empty. However when a new item is ordered, the table
shows the new as well as the already ordered old item. Where does it
store this old data? Is there some sort of cache and if so, how can I
delete it?
Thanks, Amit

[David Portas]

Are you creating a table per shopping cart? That's a lousy design. Don't do
it. Tables aren't arrays and your application shouldn't have to modify your
schema. Use a single table for all your carts, something like this:

CREATE TABLE ShoppingCart (user_id INTEGER NOT NULL REFERENCES Users
(user_id), product_id INTEGER NOT NULL REFERENCES Products (product_id), qty
INTEGER NOT NULL CHECK (qty>0), cart_status CHAR(1) NOT NULL CHECK
(cart_status IN ('O','C','Z') /* Open,Confirmed, Cancelled */), PRIMARY KEY
(user_id, product_id))

SELECT ...
FROM ShoppingCart
WHERE user_id = ??

--
David Portas
SQL Server MVP
--