December 19, 2008 at 4:05 pm
Let me present a common Order entry scenario. We have a Customer who places one to many orders and each order contains one to many products. Consider the following simplistic table structure
Order Table
OrderID
OrderDate
CustomerID
OrderItem Table
OrderItemID
OrderID
ProductID
Quantity
Customer Table
CustomerID
FirstName
LastName
Product Table
ProductID
ProductName
How would you design the stored procedure interface layer?
Would you make a prcOrder_Add that takes all data hierarchy as XML?
Would you make a prcOrder_Add that adds just an order header and then requires the lines to be added using prcOrderItem_Add etc?
How would you design the application layer?
Would you send all the data as XML into the prcOrder_Add from above?
Would you make a business object layer that includes an Order object that would then persist itself calling prcOrder_Add, prcOrderItem_Add etc?
How would your design choice hold up if you had to import the data from files. Let's say 2 million orders a day?
Finally, are there any books out there that you would recommend that deal/make use of these or better patterns?
Thanks in advance!
December 22, 2008 at 9:14 pm
I see there have been a few views; does anyone have some words of wisdom on this design conundrum?
December 23, 2008 at 8:17 am
Personally, I'd say it depends on how your application user interface is designed and how much logic you have in your business layers. If the user enters all of the order information including all the products and then saves them together, then it makes perfect sense to have a prcOrder_Add stored procedure that includes an XML parameter that has all the OrderItem rows. It will help reduce "chattyness" between the application and the database over your network, and
If your application requires the user save each line individually, then this wouldn't really help.
If you're importing from files, then it depends on your file type and structures of course. You'd process XML files differently than delimited or fixed width text files.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply