solidcodes — 2012-05-31T21:29:00-04:00 — #1
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.
solidcodes — 2012-05-31T22:13:02-04:00 — #2
nevermind I already fixed this problem.
guido2004 — 2012-06-01T01:53:11-04:00 — #3
Would you mind sharing your solution with us, for future reference to others that might encounter the same problem?
solidcodes — 2012-06-01T08:23:15-04:00 — #4
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.
guido2004 — 2012-06-01T08:29:06-04:00 — #5
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.
solidcodes — 2012-06-01T08:37:22-04:00 — #6
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.
solidcodes — 2012-06-01T08:49:12-04:00 — #7
Okay I'll try your suggestion also.
r937 — 2012-06-01T08:53:43-04:00 — #8
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
guido2004 — 2012-06-01T08:54:46-04:00 — #9
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.
solidcodes — 2012-06-01T09:01:31-04:00 — #10
So the book is wrong?
Okay since the SQL Master already spoken.
I will follow the leader lolz.
r937 — 2012-06-01T09:04:30-04:00 — #11
the book is right
solidcodes — 2012-06-01T09:09:00-04:00 — #12
Now I"m really confused.
Who is telling the truth?
What book name is right?
r937 — 2012-06-01T09:15:04-04:00 — #13
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
guido2004 — 2012-06-01T09:18:27-04:00 — #14
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.
r937 — 2012-06-01T09:19:43-04:00 — #15
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
solidcodes — 2012-06-01T09:25:00-04:00 — #16
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.