The problem: I am working for a retail site that sells various products for cars and trucks. We want to integrate eBay and Amazon API to list our products on these stores. The problem arises when I try to send Vehicle compatibility data.
Example from our DB:
Product ID | Other IDs | Make | Model *number* | *number* | Ford | F150 Crew Cab *number* | *number* | Chevy | Silverado 2500/3500 *number* | *number* | Dodge | Ram Pickup 2500-5500 HD
Example from eBay Master Vehicle List DB:
Make | Model Ford | F-150 Chevrolet | Silverado 2500 Dodge | Ram 2500
As seen from the examples eBay/Amazon doesn't recognize our Models. There is no easy solution like using a "split" function to split Models into Model and Sub-model for example, because every case is different. Sometimes Model fields have Submodel as well, sometimes they have a range of Submodels (2500-5500), sometimes Model is not correct (F150 instead of F-150) etc.
Solution: Changing every Model in the database to a correct Model format is not an option, because our system already uses these incorrect Models for business logic.
Writing a million exceptions in the code to handle incorrect Models is not very appealing as well.
The only solution I see is to create a new table and fill it manually with correct model names and their corresponding IDs for our products. This would be very time consuming task though.
Could anyone suggest a good solution for this problem?