Database state maintenance and incomplete sessions

[Luis]

This is a question with regards to using a database for maintaining
state on a site. I'll use the Shopping Cart application from the
ASPFAQ.com site as an example.

The question concerns users who start shopping but do not complete
their session and do not proceed to the 'checkout counter'.

The Shopping Cart application uses a table called Cart to track the
products that I have added to my shopping cart while shopping. If I
add a number of items to the cart and then shut down the browser with
these items still in my shopping cart these items remain in the Cart
table on the database and are not removed. On a busy production system
this could result in a very large Cart table that is full of
incomplete orders.

My question is, how is this handled in a real life production
environment? How would this Shopping Cart application be written if it
was intended for real life use so that the Cart table does not
accumulate all these incomplete orders that users have abandoned. Is
there a way to 'rollback' an order if it is detected as incomplete?

Eg. Could I use a 'temporary' table on the database to store the items
that the user has added to his basket and only commit the data to the
Cart table after he reaches a certain point in the transaction, eg.
when his credit card information has been verified and the order
submitted?

[Aaron Bertrand - MVP]

> these items still in my shopping cart these items remain in the Cart

> table on the database and are not removed. On a busy production system
> this could result in a very large Cart table that is full of
> incomplete orders.
>
> My question is, how is this handled in a real life production
> environment?

You have a job that cleans up sessions where page activity is older than the
session timeout. See the following article for some ideas:

[url]http://www.aspfaq.com/2491[/url]

Note that it uses an img src but it could also use an #include file or
something else to track the user's activity by session.

> Eg. Could I use a 'temporary' table on the database to store the items
> that the user has added to his basket and only commit the data to the
> Cart table after he reaches a certain point in the transaction, eg.
> when his credit card information has been verified and the order
> submitted?

Well, that doesn't solve the issue at all. It just places the burden of
"cleaning up" on a table with a different name - because now you have to
worry about the temporary table filling up with incomplete orders and
abandoned sessions. Right?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]