A products can be sourced from multiple suppliers.
So as it stands, I generate an order for a specific supplier and the order basically list the Product Name along with qty I require from them. They then use the product name to manually pick the product from their warehouse.
I want to improve the current system by storing the suppliers own productID. However, I can't store this in the products table, as each product can be sourced from different suppliers and each supplier will have a different productID for the same product.
Can anyone suggest how this should be stores in my database?