Hi,

I have a database with these tables (amongst others) which i've simplified a little below to help with the question.

Products (ID, Name, price, stock_level)
Suppliers (ID, Name, Address)

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?

Thanks,
Anand
_______

Products (ID, Name, price)
Suppliers (ID, Name, Address)
Supplier_Products ( Supplier_ID, Products_ID, Supplier_productcode, stock_level)