Download Report

TABLE OF CONTENTS
DATA PRODUCT OVERVIEW...............................................................................................2
What Is a VIN Pattern? ............................................................................................................ 2
Common Names......................................................................................................................... 2
Chrome VINMatch Data Model ............................................................................................... 2
DATA DEFINITIONS ..............................................................................................................4
Table Name: Category ............................................................................................................ 4
Table Name: StyleGenericEquipment ................................................................................. 4
Table Name: StyleWheelBase ............................................................................................... 5
Table Name: Version ............................................................................................................... 5
Table Name: VINEquipment .................................................................................................. 6
Table Name: VINPattern......................................................................................................... 7
Table Name: VINPatternStyleMapping ............................................................................... 9
Table Name: YearMakeModelStyle ...................................................................................... 9
USAGE ......................................................................................................................................11
Loading the Data......................................................................................................................11
Decoding a Vehicle Identification Number (VIN) ............................................................11
Common Features ...................................................................................................................12
Model Year Coverage of Chrome Category IDs ...............................................................12
DATA UPDATE METHOD .....................................................................................................13
Data Update ..............................................................................................................................13
Frequency ..................................................................................................................................13
APPENDIX – VIN CHECK DIGIT CALCULATION........................................................14
APPENDIX – OTHER CHROME DATA PRODUCTS ......................................................15
Chrome New Vehicle Data.....................................................................................................15
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
DATA PRODUCT OVERVIEW
Chrome VINMatch data provides the information necessary to identify an individual
vehicle, or group of vehicles, for any given year, 1981 through the current model year,
based on a VIN pattern. VINMatch enables you to systematically determine the Year,
Make, Model, Style, fleet availability, engine type, displacement, fuel system, forced
induction system and GVWR ranges associated with a specific vehicle. An additional
list is provided, indicating the vehicle’s various equipment as being “standard,” or
“optional.” VINMatch is updated nightly to ensure accurate and reliable information.
What Is a VIN Pattern?
A “VIN Pattern” is a partial VIN (Vehicle Identification Number), a series of letters or
digits and wild card characters that represents a set of matching VINs. A VIN is said
to match a VIN pattern when all the letters and digits defined in the VIN pattern are
found in the corresponding character positions within the specific VIN. Those defined
characters and their respective positions tell us certain things about any vehicle whose
VIN matches the pattern, and that is the information VINMatch provides.
Common Names
New to VINMatch are common names for the model and style (or “trim”) designations
of all matching vehicles. These names are carefully derived from the set of styles
matching each VIN through the VINPatternStyleMapping table, described below. This
affords a fair description of all matching vehicles in a single string.
Chrome VINMatch Data Model
The following diagram illustrates all the tables and the relationships in VINMatch.
Each table is detailed further below. In brief, the YearMakeModelStyle table
describes individual vehicles (styles), and the VINPattern table represents the VIN
patterns, including select information common to all vehicles related via the
VINPatternStyleMapping table to those VIN patterns.
The Category table abstracts the various features of one or more vehicles, such as
transmission or steering type, and the categories are linked both to styles and directly
to VIN patterns. When a category is mapped to a VIN pattern, via the
VINEquipment table, this indicates that the feature represented by the Category
record is either necessarily installed or at least available on all vehicles mapped to that
VIN pattern.
VINMatch is focused on information which can be known about a vehicle based on its
VIN, without knowing the exact model and style. Further information for exact styles,
including complete sets of standard and optional features, is available in the Chrome
New Vehicle Data product (see appendix)
Chrome VINMatch Tech Doc-US
2
Last Update: 2/1/2008 8:41 AM
.
DATA DEFINITIONS
Note: The tables are arranged in alphabetical order.
Table Name: Category
File Name: Category.txt
This table lists the Chrome category descriptions, filters and types that are referenced
in the VINPattern table, Style Generic Equipment table, and VINEquipment table.
Category types include: ABS brakes, air conditioning, anti-theft, audio, cruise control,
drivetrain, engine cylinders, forced induction, fuel type, locks, power seats, power
steering, power windows, restraint, restraint feature, roof, seat trim, steering wheel,
transmission speed and wheels. Each of these types includes a subset of Chrome
category data.
Category
Field Name
Definition
Example
Data
Type
CategoryID
Primary key for linking to the
StyleGenericEquipment and
VINEquipment tables.
1001
Integer
Description
Text description of the
category.
Driver Air
Bag
Text
255
N
CategoryUTF
This field can be used as a
type filter in cases where
only one instance of a subset
of vehicle equipment can be
physically present on the
vehicle.
Text
50
Y
CategoryType
The category type is used to
group related equipment
categories together. For
example, use the
CategoryType to
programmatically filter Style
Generic Equipment results
for a group of equipment.
Text
20
Y
restraint
Width
Allow
Null
N
Table Name: StyleGenericEquipment
File Name: StyleGenericEquipment.txt
This table shows the common generic equipment for the vehicle. The equipment is
considered “Standard” if it was the base equipment available when new, and is
considered “Optional” if it was optional equipment that was available to the customer.
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
StyleGenericEquipment
Field Name
Definition
Example
Data
Type
Width
ChromeStyleID
Foreign key to the
YearMakeModelStyle table.
Chrome’s Style ID.
5728
Integer
N
CategoryID
Foreign key to the Category
table.
1001
Integer
N
StyleAvailability
Will show “Standard” if the
equipment was part of the
base vehicle configuration or
“Optional” if the equipment
was an option available to
the customer.
Standard
Text
8
Allow
Null
N
Table Name: StyleWheelBase
File Name: StyleWheelBase.txt
This table contains the wheelbase values for a given vehicle. The values given are for
the standard wheelbase, plus and minus one value from the standard wheelbase to
accommodate rounding. This data can be useful to reduce the number of styles
identified when you have wheelbase information about a specific vehicle.
StyleWheelBase
Field Name
Definition
Example
Data
Type
Width
Allow
Null
ChromeStyleID
Foreign key to the
YearMakeModelStyle table.
Chrome’s Style ID.
5728
Integer
N
WheelBase
This value returns the
Wheelbase value (in this
case 153), one value less
(152), and one value more
(154)
152, 153,
154
Number
Y
Table Name: Version
This table contains a single record containing the data and schema version. Only one
value should be specified for each of the fields. This information may be useful to
Chrome Client Support when investigating any issues with the data. This document
describes the schema “VINData” version 3.
Chrome VINMatch Tech Doc-US
5
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
Version
Field Name
Definition
Example
Data
Type
Width
Allow
Null
Product
The name of the data
product being described.
VIN Data
Text
50
N
Data Version
The time and date that the
data was generated.
10/10/07
14:20
Date /
Time
N
Data Release ID
Internal Chrome CMS ID
number (database ID)
associated with this version
of the data.
48992
Integer
N
Schema Name
A name assigned to the data
schema.
VinData
Text
20
Y
Schema Version
The version number
associated with the data
schema.
v3
Text
10
Y
Country
The country with which the
product is associated. Valid
values are “US” and “CA”.
US
Text
2
N
Language
The language in which the
data is presented. Valid
values are “EN” and “FR”.
EN
Text
2
N
Table Name: VINEquipment
File Name: VINEquipment.txt
This table shows a list of Chrome equipment category IDs that apply to the vehicle.
All equipment will be either “Installed” or “Available”.
Chrome VINMatch Tech Doc-US
6
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
VINEquipment
Field Name
Definition
Example
Data
Type
Width
VINPatternID
Foreign key to the
VINPattern table. This
identifies a unique VIN
Pattern and Country.
21150
Integer
N
CategoryID
Foreign key to the Category
table. Chrome’s category ID
used for linking to the
Category lookup table.
1001
Integer
N
VINAvailability
Describes the availability of
common generic equipment
for the vehicle. All
equipment will be either
“Installed” or “Available.”
Installed
Text
9
Allow
Null
N
Table Name: VINPattern
File Name: VINPattern.txt
This table contains VIN IDs (a Chrome-assigned unique ID), which correspond to a
vehicle’s Country, Year, Make, Model, and Style as well as Chrome’s unique Style ID.
Other information is mapped to the VIN ID as well, such as engine and transmission
characteristics and GVWR range (where available).The following data example is for a
2001 Chevrolet Silverado 1500HD.
VINPattern
Field Name
Definition
Example
Data
Type
VINPatternID
Primary key. This identifies a
unique VIN Pattern within a
Country.
21150
Integer
VINPattern
This is the VIN Pattern.
Some of the digits may be
blanks, which should be
treated as wildcards when
decoding a VIN. The 9th
digit will always be a blank.
1GCGC13U*
1*******
Text
17
N
Country
ISO Country Code. Valid
values are “US” and “CA”.
US
Text
2
N
Year
Model year.
2001
Integer
VINDivisionName
Division name.
Chevrolet
Text
Chrome VINMatch Tech Doc-US
7
Width
Allow
Null
N
N
2000
Y
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
VINPattern
Field Name
Definition
Example
Data
Type
Width
Allow
Null
VINModelName
This is the common Model
name for all models
available for the VIN Pattern
ID.
Silverado
1500HD
Text
2000
N
VINStyleName
This is the common Style
name for all styles available
for the VIN Pattern ID.
Crew Cab
156.0" WB
Text
2000
Y
EngineType
CategoryID
Foreign key to the Category
table. Chrome’s category ID
for engine type (8-cyl, V6,
V10, etc.) mapped to
vehicle.
1052
Integer
EngineSize
Displacement of engine in
liters.
6.0L
Text
EngineCID
Displacement of engine in
cubic inches.
364
FuelTypeCategory
ID
Foreign key to the Category
table. Chrome’s category ID
for fuel type (gasoline,
diesel, gas/electric hybrid,
etc.) mapped to vehicle.
1059
ForcedInduction
CategoryID
Y
50
Y
Y
Integer
Integer
Y
Foreign key to the Category
table. This is Chrome’s
category ID for forced
induction engines. This will
show only where available.
Integer
Y
TransmissionType
CategoryID
Foreign key to the Category
table. Chrome’s category ID
for transmission type (Auto
4-Spd, Manual 5-Spd, etc.)
mapped to the vehicle. This
will show only where
available.
Integer
Y
ManualTransAvail
“Y” will show where a
manual trans is available.
“N” will show in all other
cases.
N
Text
1
N
AutoTransAvail
“Y” will show where an auto
trans is available. “N” will
show in all other cases.
Y
Text
1
N
GVWRRange
This field will display the
GVWR range of the vehicle
6,001 –
10,000
Text
50
Y
Chrome VINMatch Tech Doc-US
8
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
VINPattern
Field Name
Definition
Example
Data
Type
Width
Allow
Null
in pounds where available.
Three ranges will be
available: 0-6000, 600110,000, 10,001-14,000.
Table Name: VINPatternStyleMapping
File Name: VINPatternStyleMapping.txt
This table provides a reference to Chrome’s data products and contains Chrome styles
that are mapped to a VIN pattern.
VINPatternStyleMapping
Field Name
Definition
Example
Data
Type
Width
Allow
Null
VINMappingID
Primary key. VIN Mapping ID
41970
Integer
N
ChromeStyleID
Foreign key to the
YearMakeModelStyle table.
5728
Integer
N
VINPatternID
Foreign key to the
VINPattern table. This
identifies a unique VIN
Pattern within a Country.
21150
Integer
N
Table Name: YearMakeModelStyle
File Name: YearMakeModelStyle.txt
This table lists the Year, Make, Model, and Style information for the vehicle. It also
supplies the Manufacturers style code, Fleet availability and denotes whether the
vehicle is carried in Chromes New Vehicle Data product.
YearMakeModelStyle
Field Name
Definition
Example
Data
Type
ChromeStyleID
Primary key. Chrome’s Style
ID.
5728
Integer
Country
ISO Country Code. Valid
values are “US” and “CA”.
US
Text
Chrome VINMatch Tech Doc-US
9
Width
Allow
Null
N
2
N
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
YearMakeModelStyle
Field Name
Definition
Example
Data
Type
Year
Model year
2001
Integer
DivisionName
Division Name. Also known
as “Make” Name.
Chevrolet
Text
2000
N
SubdivisionName
Subdivision Name
Chevy
Pickups
Text
2000
N
ModelName
Model Name
Silverado
1500HD
Text
2000
N
StyleName
Style Name
Crew Cab
156.0” WB
LS
Text
2000
N
TrimName
Trim Name
LS
Text
2000
Y
MfrStyleCode
Manufacturer’s Style Code
CC15743
Text
50
Y
FleetOnly
Indicates a fleet only vehicle
style.
N
Text
1
N
AvailableInNVD
Denotes if this vehicle is
carried in Chrome’s New
Vehicle Data set.
Y
Text
1
N
DivisionID
Division ID
8
Integer
SubdivisionID
Subdivision ID
139
Integer
N
ModelID
Model ID
1397
Integer
N
AutoBuilderStyleI
D
AutoBuilder Style ID
w2001k10m
11t1
Text
HistoricalStyleID
Historical Style ID
2001101101
Integer
Chrome VINMatch Tech Doc-US
10
Width
Allow
Null
N
N
17
Y
Y
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
USAGE
Loading the Data
To use this system with an RDBMS, you will want to load the comma text files—which are CSVformatted ASCII files—into a database and set up the appropriate foreign key relationships as in the
data-model diagram above. Most relational database systems have their own, proprietary import
utilities. To avoid foreign-key-constraint violations, load the tables in the following order:
1.
YearMakeModelStyle
2.
Category
3.
VINPattern
4.
StyleWheelBase
5.
VINPatternStyleMapping
6.
StyleGenericEquipment
7.
VINEquipment
8.
Version
When you are ready to update your VINMatch 3.0 data (described below), you may wish to delete the
entire set before loading the newer data. Simply delete the tables in the reverse order, beginning
with ,Version, and then reload starting again with YearMakeModelStyle. This way you can avoid
dropping and recreating the foreign keys.
Decoding a Vehicle Identification Number (VIN)
Decoding a complete VIN involves identifying the best-matched VIN pattern in VIN 3.0 for a given
VIN. The rules for this are as follows:
1.
All defined characters in the VIN pattern must match the corresponding characters (in the
same character positions) in the VIN. Wildcard characters (“*”) in the pattern are undefined
and can match any character in the actual VIN.
2.
The VIN pattern having the highest number of matching characters (i.e., defined letters or
digits which match the VIN in the same character positions) should be selected.
3.
For all VIN patterns, the first eight character positions, as well as the tenth, are always
defined. The other character positions may or may not contain a wildcard. (This should help
narrow down a subset of patterns in an RDBMS.)
Then, once the best VIN pattern is found, one or more vehicle styles can be selected by linking the
VINPattern table—using the VINPatternID corresponding to the VIN pattern—to the
VINPatternStyleMapping table. This will produce a set of ChromeStyleID values, which identify
records in YearMakeModelStyle.
The ChromeStyleIDs can also be used to join with the New Vehicle Data product, for discovering the
full configuration options and other specifications for each style. (See appendix for more information.)
Chrome VINMatch Tech Doc-US
11
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
Common Features
However, since more than one style may be linked to each VIN pattern, it may be difficult to know
which is the right style for a given VIN. The VINEquipment table, along with VINPattern, defines
the equipment and features that apply to all vehicles matching the VIN pattern. Some features, such
as engine size, are described directly in the VINPattern records. Others are mapped by
VINEquipment, and their descriptions are found by linking through to the Category table. The
VINAvailability field in VINEquipment may be “Installed” or “Available”. “Installed” means that that
feature is installed in any style mapped to the VIN pattern; and “Available” means that the feature
may be installed, possibly as an option. VINEquipment is present on every VIN pattern for all years
of data.
Model Year Coverage of Chrome Category IDs
Chrome category ID coverage will vary for early years in the VINMatch product. Specifically, standard
and optional feature category IDs will be less densely populated in the Style Generic Equipment table
for model years 1989 through 1995 when compared to 1996 and later model years. For earlier model
years, there is a slight drop-off in the number of categories present, but major equipment items are
represented. There are no Category IDs for model years 1981 – 1988. Category IDs for engines will be
present for all model years in the VINPattern table. Please refer to the following example for
clarification.
Example:
Consider a 1995 and a 1996 Ford Taurus SE Wagon that came from the factory with the following
equipment: V6 engine, 4-speed automatic transmission, CD player, tilt steering wheel and air
conditioning. Both cars will carry the following category ID in the VINPattern table mapped to their
respective VIN IDs:
1051 (V6 Cylinder Engine)
However, any category IDs other than the engine feature group will not be carried on the 1995 car.
This means that only the 1996 car would carry the following category IDs in the Style Generic
Equipment table mapped to its VIN ID:
1102
1042
1017
1087
1011
(4-speed A/T)
(Front Wheel Drive)
(CD Player)
(Adjustable Steering Wheel)
(A/C)
Chrome VINMatch Tech Doc-US
12
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
DATA UPDATE METHOD
Data Update
The Chrome VINMatch data can be downloaded from Chrome’s HTTP update site at
http://update.chrome.com, using your assigned User ID and password. All model years are stored in
a single zip file named VINDATA.zip
The zip file contains the following files:
•
VINPattern.txt
•
VINEquipment.txt
•
StyleGenericEquipment.txt
•
Category.txt
•
YearMakeModelStyle.txt
•
StyleWheelBase.txt
•
VINPatternStyleMapping.txt
•
Version.txt
Frequency
The Chrome VINMatch product is updated on a nightly basis. You may update your data from the
website noted above on a schedule that is appropriate for your business model.
Chrome VINMatch Tech Doc-US
13
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
APPENDIX – VIN CHECK DIGIT CALCULATION
Chrome VINMatch Tech Doc-US
14
Last Update: 2/1/2008 8:41 AM
CHROME SYSTEMS, INC.
CONFIDENTIAL
CHROME VINMATCHTM
APPENDIX – OTHER CHROME DATA
PRODUCTS
Chrome VINMatch can be paired with any of Chrome’s new vehicle data products
through the use of the Chrome Style ID. The Chrome Style ID is a unique and
permanent identifier across all of Chrome’s data offerings.
Chrome New Vehicle Data
New Vehicle Data is a complete source of information, with over 80 unique data
points, on over 40 major vehicle makes sold in the U.S. It provides the most accurate
new vehicle data on the market and contains information from 1997 forward. New
Vehicle Data comes in a zip format containing comma-delimited, tilde-defined text files
(CSV).
Use the Chrome Style ID in the VIN Pattern table to map to the Styles table in New
Vehicle Data once a VIN has been decoded.
New Vehicle Data is updated nightly to ensure our clients always have the most
accurate data available. Canadian data is also available.
Chrome AutoBrief
AutoBrief is a quick and easy method to get detailed expert reviews on today's cars
and trucks. The reviews are at the model level and use the Chrome Model ID so that
you can link back to any Chrome New Vehicle Data product. This product examines a
vehicle's overall strengths, the changes from last year's model, the value that it brings
to the consumer and a summation of all the key points.
AutoBrief reviews are updated weekly and match the same level of coverage as our
New Vehicle Data products.
Chrome VINMatch Tech Doc-US
15
Last Update: 2/1/2008 8:41 AM