design for vehicle identification number (VIN)

Question

I've designed a few Vehicle Identification Number (VIN) decoders for different OEMs. The thing about VIN numbers...despite being somewhat standardized, each OEM can overload the character position codes and treat them differently, add "extra" metadata (i.e. asterisks pointing to more data outside the VIN number), etc., etc. Despite all that, I've been able to build several different OEM VIN decoders, and now I'm trying to build a GM VIN decoder, and it is giving me a headache.

The gist of the problem is that GM treats the vehicle attributes section (position 4,5,6,7) differently depending on whether it is a truck or a car. Here is the breakdown:

GM Passenger Car VIN breakdown

GM Truck VIN breakdown

Normally what I do is design my own crude ETL process to import the data into an RDMBS - each table roughly correlates with the major VIN breakdown. For example, there will be a WMI table, EngineType table, ModelYear table, AssemblyPlant table, etc. Then I construct a View that joins on some contextual data that may or not be gleaned directly from the character codes in the VIN number itself (e.g. some vehicle types only have certain vehicle engines).

To look up a VIN is simply a matter of querying the VIEW with each major character code position breakdown of the VIN string. For example, an example VIN of 1FAFP53UX4A162757 breaks down like this in a different OEM's VIN structure:

| WMI | Restraint | LineSeriesBody | Engine | CheckDigit | Year | Plant | Seq   |
| 123 | 4         | 567            | 8      | 9          | 10   | 11    | 12-17 |
---------------------------------------------------------------------------------
| 1FA | F         | P53            | U      | X          | 4    | A     | ...   |

GM has thrown a wrench into this...depending on whether it is a car or truck, the character code positions mean different things.

Example of what I mean - each ASCII table below correlates somewhat to a SQL table. etc.. means there is a whole lot of other columnar data

Passenger Car

Here's an example of position 4,5 (corresponds to vehicle line/series). These really go together, the VIN source data doesn't really differentiate between position 4 and 5 despite the breakdown illustrated above.

| Code (45)| Line  | Series   | etc..
--------------------------------------
| GA       | Buick | Lacrosse | etc..

..and position 6 corresponds to body style

| Code (6) | Style         | etc..
--------------------------------------
| 1        | Coupe, 2-Door | etc..

Trucks

..but for trucks, the structure is completely different. Consider position 4 stands on its own as Grosse Vehicle Weight Restriction GVWR.

| Code (4) | GVWR     | etc..
-------------------------------
| L        | 6000 lbs | etc.. 

..and positions 5,6 (Chassis/Series) now mean something similar to position 4,5 of passenger car:

| Code (56) | Line  | Series  | etc.. 
---------------------------------------
| RV        | Buick | Enclave | etc..

I'm looking for a crafty way to resolve this in the relational design. I would like to return a common structure when a VIN is decoded -- if possible (i.e. not returning a different structure for cars vs. trucks)

Answer 1

Based on your answer to my comment regarding if you can identify the type of vehicle by using other values, a possible approach could be to have a master table with the common fields and 2 detail tables, each one with the appropriate fields for either cars or trucks.

Approximately something like the following (here I am guessing WMI is the PK):

Master table

| WMI | Restraint | Engine | CheckDigit | Year | Plant | Seq   |

| 123 | 4         | 8      | 9          | 10   | 11    | 12-17 |

Car detail table

| WMI | Veh Line | Series | Body Type |
| 123 | 2        | 3      | 4         |

Truck detail table

| WMI | GWVR | Chassis  |Body Type |
| 123 | 7    | 8        | 9        |

Having this, you could use a unique select to retrieve the needed data like following:

Select *
  From
(
   Select M.*,
          C.*,
          Null GWVR,
          Null Chassis,
          Null Truck_Body_Type
     From Master_Table M
     Left Join Car_Table C
       on M.WMI = C.WMI
   Union
   Select M.*,
          Null Veh_Line,
          Null Series,
          Null Car_Body_Type
          T.*
     From Master_Table M
     Left Join Truck_Table T
       on M.WMI = T.WMI
)

As for DML SQL you would only need to control prior to insert or update sentences whether you have a car or a truck model.

Of course you would need to make sure that only one detail exists for each master row, either on the car detail table or on the truck detail table.

HTH