Architecting Database and Data Warehouse Solutions

Worobec Consulting

Architecting Database and Data Warehouse Solutions

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 Logical Data Model (LDM).  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 LDM 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.

Logical Data Model shown as an Entity-Relationship Diagram

Case Study:  West Coast Bargain Books

Business Function Model in Outline Form

 

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

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.  A screen print of the main menu is shown below.