I'm getting to the near end of my database project. It stores rates for logistics for the past 10 years. I've run into a major problem though - over the years, the same product has changed ID numbers. This causes a problem when I try to marry it up with a table storing shipment volume by product ID number - the tables will only link if the product number matches what is in the volume table, and searches for the new product number get dropped.
Further complicating the matter, sometimes the newer product number was used previously on a different product. There was no "assigned" number for a product - whatever number was available was used.
Is there a generally-accepted way to deal with this? I'd hate to lose the ability to marry product info to shipment volume.