1

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
  • description
  • carrierID
james_bond
  • 6,778
  • 3
  • 28
  • 34
Michael Libman
  • 73
  • 1
  • 10
  • this has nothing to do with PHP. If you're having problems with designing database, go learn database concepts.If you don't know how to write SQL, read a book about it. If you don't know how to use PHP with database, Google for it! – ahmet alp balkan Jul 06 '11 at 21:45
  • You are looking for a many to many relationship. You say it yourself in your description. You need another table to hold the relationships. – Jacob Jul 06 '11 at 21:47
  • 1
    .. and since you try to solve it with 1-to-many it shows that you don't understand that concept – Karoly Horvath Jul 06 '11 at 21:49
  • See this example: You need a 3rd table, often called accosiation table or junction table: http://stackoverflow.com/questions/5812680/many-to-many-relationship – ypercubeᵀᴹ Jul 06 '11 at 21:51

3 Answers3

1

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.

Brian Hoover
  • 7,861
  • 2
  • 28
  • 41
0

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

Keith Hall
  • 38
  • 5
0

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)
Marc B
  • 356,200
  • 43
  • 426
  • 500