Database structure help

[Nigma]

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,
A.

[bpat1434]

Sounds like you need a join table. A table in between products and suppliers which stores the product id, supplier id, and stock id.

Then when selecting data about a product, you can also grab the data about the the supplier and their product ID (if any).