webfoo - 12:38 pm on Aug 1, 2013 (gmt 0)
This sounds like the way to go...
I'd need to reference each product with a dual key of supplier number and SKU number
Yes this is ultimately what I decided to do. SKUs are unique within a supplier. So if the orders table says that a customer ordered SKU 30564 from suppler 9, it will always be the same product, regardless of which row id that product currently sits in.
The entire process of FTPing the file from the supplier, unzipping it, erasing the old records, and inserting the new records takes only 2 or 3 minutes. This is an amazing improvement on 40 hours. It could probably be made more efficient, but it is good enough for the purposes of this system. This process is scheduled to run at off-peak hours in the middle of the night so 2 minutes of downtime is not an issue. The system is only used during business hours anyway.