How to make One to Many relationship?

Hi guys

I have 2 tables.

Table1: products
Table2: product_photo

Can you show me the Data Models and relationship of these tables.

Let say one product can have one or more photos.

samples?

Thanks in advanced.

nevermind I already fixed this problem.

Would you mind sharing your solution with us, for future reference to others that might encounter the same problem?

That’s really very basic.

I have 2 tables,

Table1: product
Table2: photos

Then I created a junction table
Table3: product_photos

[B]product[/B]
====================================
prodid | prod_name |  more columns here....
====================================
 _____|___________|____________
______|___________|____________

[B]photo[/B]
====================================
photoid | filename
====================================
______|__________|________
______|__________|________


[B]product_photos (junction table)[/B]
====================================
id | proid  | photoid |
====================================
__|______|_______|
__|______| _______|

It’s just the basic of one to many relationship.

No it isn’t. A junction table is for many to many relationships.

If 1 product can have 1 or more photos, and 1 photo can belong to only 1 product, then all you have to do is add a product_id column to the photos table. And if possibile, you can declare it a foreign key to the id column of the products table.

@guido2004

sorry but I’m following the WROX Beginning MySQL.
So you mean the book is wrong?
I don’t think so.

http://p2p.wrox.com/book-beginning-mysql-169/

You can download the .PDF and You can read it in Chapter 4. below is the link.

Okay I’ll try your suggestion also.

i don’t think so either – i think it’s you that’s wrong :slight_smile:

i looked at that chapter 4 you linked to, and it doesn’t mention products and photos anywhere

can the same photo be used for multiple different products? if not, it’s a one-to-many relationship

if it really is a many-to-many, then, yes, you would need a junction or association table… but note, it should ~not~ have its own id column

You can use a junction table in one to many relationships as well, and put a unique index on (in this case) the photoid column, to avoid assigning the same photo to more than 1 product. But I don’t see what the benefit is.

Let’s see what @r937; has to see about it.

Oops, he already found us :lol:

Rudy, the book does use a junction table when explaining how to handle a 1 to many relationship, even if the example isn’t about products and photos.

So the book is wrong?
Right?

Okay since the SQL Master already spoken.
I will follow the leader lolz. :slight_smile:

wrong

the book is right

:smiley:

Now I"m really confused.
Who is telling the truth?
What book name is right?

i disagree

there are two examples illustrating the one-to-many relationship – authors-authorbooks, and books-authorbooks

authorbooks isn’t a junction table for a one-to-many relationship, it’s a junction table for a many-to-many relationship

a very unfortunate and possibly misleading example, in my opinion

Ah yes, I see. Very unfortunate, indeed. It supposes, without explaining, that the reader will understand that the example as a whole is a many to many relationship, but each single relationship between those three tables is a 1 to many relationship. A bit far fetched for a beginners book IMO.

forget that chapter for a moment and focus on this –

a many-to-many relationship is always implemented as two one-to-many relationships involving a common junction table

a one-to-many relationship does ~not~ involve a junction table, even if the “many” table is actually a junction table for a many-to-many relationship

if a one-to-many relationship actually did involve a junction table, then the data model in that chapter would be…

authors – authorauthorbooks – authorbooks – bookauthorbooks – books

like i said, using authors-authorbooks and books-authorbooks to illustrate one-to-many relationships is unfortunate and potentially misleading

… but not wrong :slight_smile:

Okay, okay I will follow you guys.
Since your example for 1-many relationship solution, without the junction table is much easier and practical to use.

Thank you for your help guys.