Page 232 -

Ca Se StUD y 5 Case Study 5 231

Searching for Classic and Vintage Car Parts…

Shane Ashley-Carter is a highly qualified and experienced car inventory system that he had successfully used previously was
mechanic, having served apprenticeships with both British luxury becoming cumbersome, unwieldy, and increasingly inaccurate.
car marquees Jaguar and Bentley in the early part of his career, Once the economic recovery began in 2013, demand for
which now spans more than 20 years. He is a typical luxury his classic and vintage car parts increased again as his car
car mechanic, whose expertise and experience is sought after in repairs and restoration business expanded. At this point, he
the United Kingdom and world-wide, including the Middle-East, decided that he would minimize his stock of parts as quickly
Africa, Asia, Australia, and the United States. Shane specializes as possible by capitalizing on the available demand for his
in classic and vintage models as well those in the current ranges. services. However, Shane had two problems. First, he did not
In his spare time, he also renovates and repairs interior fixtures reliably know which classic and vintage car parts he had in
and fittings, and exterior body panels at his workshop in Oxford- stock and where they were located in his workshop. Second, he
shire, England. (See http://www.a-cl.co.uk). During the months of wasn’t willing to exhaust himself climbing the storage racks
June, July, and August, he works on repairs and restorations of in order to locate components that are sometimes small in size.
car exteriors and interiors when the weather is reliably warm and To address the problems, Shane created a Microsoft Access
dry, and during the remainder of the year he repairs and replaces database with only one table: Part. To populate the database
mechanical components. with data, Shane had to initially take stock of all the classic and
Before the global financial crisis of 2008, he did a brisk vintage car parts and record the data as shown in the columns
trade in acquiring, restoring, renovating, reselling, and ret- of Figure 5-33.
rofitting car parts to classic and vintage Jaguars and Bentleys, Obviously, a single-table database could have been stored
especially of the former because of the global brand recognition in Excel, but Shane needs to query his data in a number of dif-
that resulted from the famous Oxford-based detective television ferent ways. For example, he wants to know the location of the
series, Inspector Morse. During this time, he rarely needed to engine temperature sensors in his workshop racking system
maintain significant stocks of the various car components as that have a temperature range suitable for the cooler conditions
there was a balance between supply and demand. However, dur- of the Scotland region. He also wants to know which fuel sys-
ing the period leading up to 2010, he found that his stock of car tem component parts are available for different grades of fuel.
parts increased due to a severe down-turn in the world economy, Further, customers have special needs. For example, one might
which significantly affected his trade in fitting and reselling clas- want an exhaust system component that generates a particu-
sic and vintage car parts. At one point he had a stock of 200 car lar sound note during acceleration, which is in accord with the
components. original manufacturer’s specification of the classic and vintage
Clearly, 200 car components can range in size and complex- car. In the absence of a database, he doesn’t know whether he
ity depending on their location in the classic and vintage cars, has the part or not. Alternatively, he might want to know of all
from small temperature sensor units to large engine components of the electrical parts concerned when he decides that he needs
and their assemblies and sub-assemblies of ancillary systems as to refurbish an electrical part with a new component, and so
are found in fuel, exhaust, and electrical systems. Initially, Shane on. Because of his changing needs, particularly concerning his
stored them in his workshop in the rack system that he had questions regarding the data stored in his database and his vast
been extending as the number of parts grew. Although his busi- reserve of motor parts, Shane uses the Access query facility.
ness had slowed significantly, he found that he was spending an Figure 5-34 shows an example query that returns the location
increasing amount of time managing access to his stock of parts of temperature sensors in the workshop racking system that
and determining their availability for customers’ classic and is suitable for the Scotland region, and Figure 5-35 shows the
vintage car repairs and restorations. The manual labeling and result of that query.