This is my first database I am designing.

However I am getting confused on the many to many relationships.

I have a products table, each product can have inventories in many different warehouses. So one product can have many inventories (in different warehouses)

Table 1 -
code | name | cost_price

Table 2 - (Many to Many Bridging table)
prod_code (foreign key of products.code) | inv_code (foreign key of >products.code)

Table 3 -
inv_code (auto increment) | warehouse_code (FK of warehouse.code) | >inventory_count

Table 4 -
code | warehouse_name

My big questions is: is this correct? and 2: does table 3 need product.code linked to it? or is it already linked through the bridging table? (product_inventory) If it is linked, what is an example of an SQL query to input an inventory for a product, so that Table 2 populates to link it all back so later I can do a join to get the inventory for the product again.

Any tips are well appreciated!!

Does every primary key need to have a foreign key? I.E should inventory.code be a foreign key to product_inventory.inv_code? or do only doubles (not the original need to be the foreign key) if so which should be the copy (foreign key) the bridging table or the inventory table inv_code.

Thanks in advance!


Why have product_inventory at all?


prod_code (foreign key of products.code) | warehouse_code (FK of warehouse.code) | >inventory_count
  • Because I thought many to many relationship needed a bridging table? Product can have more than one warehouse with different inventory per product and warehouses can have more than one product inventory? – Jonny B'Good Jun 26 '16 at 9:28
  • Why do you think you need a many to many. A product has zero or more warehouses. What you you need to do that is not satisfied with the design in my answer. – paparazzo Jun 26 '16 at 9:31
  • 1
    So it would be acceptable to have Product 1 | warehouse 1 | inventory 1<br> Product 1 | warehouse 2 | inventory 4<br> Product 1 | warehouse 3 | inventory 4<br> Product 2 | warehouse 1 | inventory 1 etc? – Jonny B'Good Jun 26 '16 at 9:33
  • The more I think about it, the more I see you are right, thankyou for your help. if it was many to many would that be the correct way of doing it though? – Jonny B'Good Jun 26 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.