Introduction to the CAP database
Introduction to the CAP database
Introduction to the CAP databaseThe CAP database is a database of all cars and technical details sold in the UK maintained by CAP Motor Research Ltd. The database is an invaluable source of information on UK Cars and contains a wealth of technical information for each model of car, optional extras for new cars, standard features, pricing data and stock images. At present the database holds some 46,000 car details and the average release from CAP weighs in at around 6GB.
Updates to the database are fairly regular with price data updated on the first of every month.
At first glance the database is a giant maze and, even after reading the documentation supplied by CAP, finding your entry point and understanding the structure of the database can take several confusing hours.
The key to the database is a CAP ID, this value uniquely identifies a Car within the database and almost all information in the database on this car references this ID. The best place to start when locating the CAP ID for a car are the tables CapDer and CAPVehicles.Often CAPVehicles is the easiest table to use when receiving input from a user as it contains the Manufacturer, (Short) Model and (Short) Derivative text for each record. In the CAPVehicles table the field Cvehicle_ID contains the value of the CAP ID for the record. CapDer is not as user friendly as CAPVehicles but it does reference the manufacturer, model and derivative text in other tables and contains information on the number of doors, drive train, fuel delivery, transmission and fuel type for the car. The CAP ID in this table can be found in the field cap_ID. Joining these two tables in an SQL query produces a powerful result set for use in search results or basic car details forms / pages.
Once you have the CAP ID for a vehicle almost every other table uses this value in a one to one or many to one relationship. NVDTechnical for example references the CAPVehicles / CapDer table by holding the CAP ID value in TECH_Id and NVDOptions does the same with the field OPT_Id. The manual available form CAP has more detailed information on how the tables reference one another.
The only exceptions to this rule is NVDImage which holds the stock images of the cars and the dictionaries which describe technical and options data. The dictionary data is often referenced by a field similar to TECH_TechCode or OPT_OptionCode, again check the manual for more details, image data however is different.
To keep the size of the database to a minimum and because many variants of a car share the same stock photo to access the appropriate stock image of the car you must first obtain the Image ID from the NVDModelYear table. The NVDModelYear table is likely to have multiple rows for a single CAP ID and may have different images depending on when the car in question was manufactured. In the new vehicle database whenever an effective from / to or introduction date can be found you should expect to find multiple records for one CAP ID. CAP Motor Research add rows to these tables as the details of a car are changed by the manufacturer.
You should query the NVDModelYear table using your CAP ID and the date the vehicle was manufactured. Images for cars which can still be ordered from the manufacturer will have the value of NULL in the MY_EffectiveTo field. The ID of the image can be found in the MY_ImageID.
Once you have the ID it is simply a case of retrieving the image data from the NVDImage table by selecting the record with ima_imageid having the same value you found in MY_ImageID. Image data found in the CAP database is always in a JPEG format and has a resolution of 1024x768.