Early in 2004, West Coast Bargain Books was looking for someone to upgrade a number of home grown MS Access databases to SQL Server to solve some performance issues. They thought it would be about a two week effort. While validating the scope of the project, it quickly became apparent that the performance issues were related to more fundamental problems. Since no data modeling had ever been done, the multiple Access databases were inconsistent and suffered from major design flaws. The recommendation was to not upgrade the existing system to SQL Server since few of the current problems would be solved.
Faced with a flawed system and no upgrade path, they asked what they could do. The philosophy of Worobec Consulting has always been to buy something off the shelf if it meets your needs. If no system exists in the marketplace and significant competitive advantage can be gained by building a customized solution, then go for it. In either case, build or buy, you need a couple of high-level models to help make the right decisions early in the project.
We conducted a two-day facilitated session to product a Business Function Model (BFM) and a Conceptual Data Model (CDM). These two models are necessary whether you are building or buying. If buying, the BFM can serve as a functional checklist to evaluate potential systems and the CDM can be compared to the database design to determine whether all data identified in the modeling session is supported. If building, the two models help to define scope and form the basis of the conceptual design. The two models produced during the session are shown below.
Business Function Model
Main Function: Buy and Sell Books and Related Media Products
Buy books
Find vendor
Determine books to carry
Evaluate / negotiate a deal
Place a purchase order
Arrange shipping
Receive books
Create space / location
Sort books
Locate books
Record physical attributes (remainder mark, condition, etc)
Offer books for sale
Allocate books to a channel (reserved or not)
Price books (subjective or calculated)
Generate list / sample / show
Receive an order
Record
Who
What
Where to send
How to send
When to send
Meet customer needs (no peanuts, sticker books etc)
Approve credit / payment
Fulfill an order
Generate pull sheet
Pull books
Prep books
Pack books
Determine freight requirements
Finalize invoice
Collect payment
Process returns and claims
Conceptual Data Model
http://bruceworobec.wordpress.com/2008/08/12/case-study-west-coast-bargain-books/wcbb/
Armed with the results of the modeling session, WCBB went of to search the marketplace for a suitable system. The systems they found either did not fully meet their needs or were too expensive. After much debate the decision was to build a custom solution. SQL Server was chosen as the database and Access was chosen as the front end to leverage the expertise they had in house. The system went live in January, 2006. The finished system took approximately 600 hours of Worobec Consulting time and probably twice that number of hours spent by their internal resources. An additional project objective was to train their staff to perform ongoing support and database administration of the system.
