I have 2 tables.
Can you show me the Data Models and relationship of these tables.
Let say one product can have one or more photos.
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,
Then I created a junction table
prodid | prod_name | more columns here....
photoid | filename
product_photos (junction table)
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.
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 :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?
Okay since the SQL Master already spoken.
I will follow the leader lolz.
the book is right
Now I"m really confused.
Who is telling the truth?
What book name is right?
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.
This topic is now archived. It is frozen and cannot be changed in any way.