What is the best way to store a shopping list or order?

[JRoughgarden]

After a customer decides to buy a shopping list, there is generally a
need to store/insert one master record and a variable number of child
detail records, preferably all wrapped in a transaction. There are lots
of ways to do this and I am wondering if anyone knows which is most
efficient.

One approach is to use ADO.NET's transaction capabilities, define
single-record insert procs for the master and detail tables, and call
the detail insert in a loop from the web page. This has N+1 trips to
the server, which is not too attractive.

Another approach is to concatenate all the data into a big
string/varchar variable and pass it to a decoder proc that would then
call the single record insert procs via a loop inside the decoder proc.
This second approach would use T-SQL's transaction capability and have
only one trip to the server, but it is more effort to code on the web
page and in the decoder proc.

Surely this is a common problem. Are there any more elegant/efficient
methods that anyone can suggest? Can one pass an array to a proc? Is
this a place for a user defined data type?

Any advice is much appreciated.

[xAvailx]

>which is most efficient. <<

I would start by first focusing on correctness before worring about
efficiency. Does the system you are working on have such high traffic
requirements that a few insert statements would make such a big
difference? I don't know your requirements, but I find that too many
developers worry about these types of issues too much and often come up
with unnecessary hacks for no good reason. Having said that...

>Surely this is a common problem. Are there any more elegant/efficient methods that anyone can suggest? Can one pass an array to a proc? Is this a place for a user defined data type? <<

You may want to look into passing the data as XML. See this link for an
example.

http://msdn.microsoft.com/library/de...ml/sql01c5.asp

HTH


JRoughgarden wrote:

After a customer decides to buy a shopping list, there is generally a
need to store/insert one master record and a variable number of child
detail records, preferably all wrapped in a transaction. There are lots
of ways to do this and I am wondering if anyone knows which is most
efficient.

One approach is to use ADO.NET's transaction capabilities, define
single-record insert procs for the master and detail tables, and call
the detail insert in a loop from the web page. This has N+1 trips to
the server, which is not too attractive.

Another approach is to concatenate all the data into a big
string/varchar variable and pass it to a decoder proc that would then
call the single record insert procs via a loop inside the decoder proc.
This second approach would use T-SQL's transaction capability and have
only one trip to the server, but it is more effort to code on the web
page and in the decoder proc.

Surely this is a common problem. Are there any more elegant/efficient
methods that anyone can suggest? Can one pass an array to a proc? Is
this a place for a user defined data type?

Any advice is much appreciated.

[JRoughgarden]

You may want to look into passing the data as XML. See this link for an
example.

http://msdn.microsoft.com/library/de...ml/sql01c5.asp

HTH

Thanks for the URL reference. I think XML may be the way to do this;
beats writing your own code/decode routines.