Using MySQL 1 to many relationship

I currently don't have any code yet. I understand what a 1 to many joined relationship is I just don't know how to make it work. The idea here is that many carriers can have the product and vice versa. How can I keep track of which carriers has which products if each table has only 1 field to keep the others ID in? Here's the example:

Table: Carriers

• id
• name
• productID

Table: Product

• id
• name开发者_如何学Python
• description
• carrierID

Michael, Either the carriers table or the product table has one too many bit of information. With this table structure, you would wind up having to create a carriers line per carrier/product and a product line per product/carrier

One way to think about this is to have three tables:

Table: Carriers

• ID
• name

Table: Product

• ID
• name
• description

Table: product_carriers

• carrier_id
• product_id

With this type of structure, you could get all the information off of the product_carriers table, and the product and carriers table would be unique per product and carriers, eliminating duplicate information and speeding up your queries.

you have a third table, containing carrierid and productid and populate with the relationship

e.g.

cid,pid
1,1
1,2
2,1
2,4

carrier id 1 is related to product id 1 and 2 carrier id 2 is related to product id 1 and 4

product id 1 is related to carrier id 1 and 2

This is actually a many-many relationship. Many carriers, each carrying one or more (many) products. To accomplish this, you need a 3rd table to produce the linkages:

carrier_products:
    carrier_id (foreign key to carriers) 
    product_id (foreign key to products)