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.
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.
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
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
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.
wrong
the book is right
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
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.