SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    nevermind I already fixed this problem.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Would you mind sharing your solution with us, for future reference to others that might encounter the same problem?

  4. #4
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    That's really very basic.

    I have 2 tables,

    Table1: product
    Table2: photos

    Then I created a junction table
    Table3: product_photos


    product
    ====================================
    prodid | prod_name | more columns here....
    ====================================
    _____|___________|____________
    ______|___________|____________

    photo
    ====================================
    photoid | filename
    ====================================
    ______|__________|________
    ______|__________|________


    product_photos (junction table)
    ====================================
    id | proid | photoid |
    ====================================
    __|______|_______|
    __|______| _______|


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

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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.

  6. #6
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    @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.
    http://media.wiley.com/product_data/...0764579509.pdf

  7. #7
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Okay I'll try your suggestion also.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by solidcodes View Post
    sorry but I'm following the WROX Beginning MySQL.
    So you mean the book is wrong?
    I don't think so.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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.

  10. #10
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So the book is wrong?
    Right?

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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by solidcodes View Post
    So the book is wrong?
    Right?
    wrong

    the book is right

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Now I"m really confused.
    Who is telling the truth?
    What book name is right?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by solidcodes View Post
    Now I"m really confused.
    Who is telling the truth?
    What book name is right?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    933
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •