Thursday, March 31, 2005

Another Alternative to AdventureWorks

Before the days of COM-based VB6 Web Services, I wrote a VB6 client/server demonstration project that emulated early Web-based retail supply chain management (SCM) applications. The "smart client" front end generated orders for consumer electronics products, and included full support for drop shipments from one of three distributors. Unlike AdventureWorks and Northwind, the project included invoicing, credit-card processing, and drop-shipment purchasing from and invoicing by the distributors. Freight charges were obtained from the UPS Web site. The unified client contained about 20,000 lines of code. The SQL Server 2000 back end consisted of six databases: OCE_Cust for sales order processing and invoicing, OCE_Prod for warehousing and shipping, OMB_Network (OmegaBank) for credit card processing, and three distributors: AlphaDist, BetaDist, and GammaDist. The design was based on separate physical locations for each database. For an early book on .NET Web services, I upgraded the Windows client to VB 2002 (with the Wizard) and converted the credit-card and distributor classes to .NET Web services. A flow diagram, description of the project, typical XML document instances and schemas, and links to WSDL documents and database diagrams are here. The final step was conversion of the client—again with the Upgrade Wizard—to a single ASP.NET Web page, as shown here (click the image to open it in a new window):

The page won't garner any design awards, but it displays all information required to process an incoming order, which takes from 0.5 to slightly over 1 second when generating XML purchase and invoice documents for outsourced items. A live demonstration of the project is available here. My April 2002 "Speed SQL Server Data Access" article discusses relative performance of the three successive versions of the OCE project (see Table 1 on page 2).
Note: The Web Services Interoperability Organization ( subsequently created a sample Retailer SCM interoperability test application that's similar to—but much simpler than—the OCE project. WS-I requested permission to republish the flow diagram and some content from the OakLeaf site, which I granted, but I haven't seen any evidence of their use of the material.
AdventureWorks consolidates human-resources, sales, purchasing, and production data in a single database. In the real world, the tables for each schema undoubtedly would reside in separate databases that are under the control of the related groups. Microsoft is heavily promoting service-oriented architecture (SOA) with autonomous Web services, but AdventureWorks' single-database design isn't suited to SOA. A version with four (or more) individual databases, simplified table structures, and more complete sample data would be better suited to the needs of VS 2005/SQL Server 2005 developers and book writers.