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!