Best Practice? Shopping Cart Table data

Question

Best Practice? Shopping Cart Table data

bkirk

SSCrazy

Points: 2824
More actions
June 1, 2010 at 9:56 pm

#223216

I'm developing the architecture for a ASP.NET website with a shopping cart SQL table for storing products, quantities, etc... when a person clicks the 'add to cart' button.
I have all the schema designs of my 3 tables:
1) ShoppingCart
2) OrderSummary
3) OrderDetails
However i was wondering how i can move the ShoppingCart contents for an order into the OrderSummary/OrderDetails tables once a person is ready to 'Checkout'.
The Add to Cart process into the ShoppingCart table is easy enough via simple INSERT behaviour.
However, when a person confirms the order should i trigger a Stored Procedure to create 1 line in my OrderSummary Table, and then some sort of LOOP to insert 1 or many lines in my OrderDetails?
It sounds like i might need to build a pretty sophisticated TRANSACTION within my SP?
Any guidance would help on how best to do this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Why do you think you need a loop? SQL works best with set-based operations, not row-by-row operations. Yes, you're going to need some transaction+error handling to do this properly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

bkirk (6/1/2010)
I'm developing the architecture for a ASP.NET website with a shopping cart SQL table for storing products, quantities, etc... when a person clicks the 'add to cart' button.
I have all the schema designs of my 3 tables:
1) ShoppingCart
2) OrderSummary
3) OrderDetails
However i was wondering how i can move the ShoppingCart contents for an order into the OrderSummary/OrderDetails tables once a person is ready to 'Checkout'.
The Add to Cart process into the ShoppingCart table is easy enough via simple INSERT behaviour.
However, when a person confirms the order should i trigger a Stored Procedure to create 1 line in my OrderSummary Table, and then some sort of LOOP to insert 1 or many lines in my OrderDetails?
It sounds like i might need to build a pretty sophisticated TRANSACTION within my SP?
Any guidance would help on how best to do this?

Shopping carts are actually a pretty serious affair and if you screw it up, the customer will end up with a very, very bad impression. That kind of impression spreads like wild fire and the company you're doing this for (if it isn't homework) will get a bad rep almost overnignt.

If this isn't a homework project, my recommendation is that (sorry) you just don't know enough to do this correctly and you should get some professional help in building all of this. Do it for your customers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

iirc there are commercially available shopping cart components for ASP.Net. May be cheaper to buy one of those than reinventing the wheel.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I agree with Gail... the precanned ones are "certified" in many cases and come with all the goodies including Luhn Mod 10 Checksums, Security, card validation on the fly, instant processing (you get your money quicker), etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions