Need best practice: searching through car options

[mikethecoder]

So here is my problem...

 

I work at a company helping do automotive/vehicle websites... a particular dealer wants to build a new search page that will search through vehicle options and equipment. The options are actually sent to us each night in a data feed and imported into the database. The database basically has an OPTIONS table... the fields are basically just "option" and "vin"... so an example of the db would be:

 

VIN              |  OPTION

-----------------------------

72838723HHD  | Cd changer 6disc

72838723HHD  | halogen headlights

72838723HHD  | Car bra included

11111929J323  | 4 disc cd changer

 

The problem is that we don't control the option data since it is sent to us from another company. A cd player could be listed in 100 different ways across the vehicles of a particular dealer. They want a search page where we would list things like "CD Player" and a checkbox to search for cars with that option. I was just wondering if anyone has any advice on the best way to do this or if they have done anything similar. I am guessing for the checkbox values I would just have general keywords and use a lot of SQL LIKE commands... maybe if they check cd player it would add to the SQL "where option like '%cd%' and option like '%cd player%'" or something like that. They also want to have a page where it shows all the options and in parenthesis show how many matching cars for each option. Just wanted some thoughts from experience here.

 

Thanks everyone!