Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Alternative many to many approach

    Hi !!
    When a many to many relationship is required, the linking table is the only way to go or does any other option exists?

    Suppose the following scenario

    One author - One book (90 %)
    One author - many books (8 %)
    Many authors - One book (2 %)

    Could the following design be an option or is it totally wrong?

    Click image for larger version. 

Name:	AuthorsBooks2.JPG 
Views:	66 
Size:	18.9 KB 
ID:	8323

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the linking table is the best way to go

    your diagram doesn't really convey the idea of a working many-to-many scenario

    perhaps you could illustrate how it works by showing us a few rows of data from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Authors and books are not the entities in the real scenario but my english is not good enough to describe exactly the real scenario.

    Here are the rows for authors table
    Click image for larger version. 

Name:	AuthorsRows.JPG 
Views:	47 
Size:	8.1 KB 
ID:	8324

    and for books table
    Click image for larger version. 

Name:	BooksRows.JPG 
Views:	39 
Size:	7.7 KB 
ID:	8325

    In the real scenario the entityB (books) will be autogenerated by EntityA (Authors). This will happen only once. So having bookID in the Authors table will restrict author of collaborating more than once but in my case this is not a problem. The entities A (authors) already exists in the table. My programm is going to insert mostly (98%) entity B rows in the Books table. The rest 2 % of entities B that are result of collaboration are going to modify the EntityA (Authors) Table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is an incomplete many-to-many relationship, specially crippled for your particular needs -- an author can collaborate only once

    i would nevertheless still use a proper relationship table

    what you should do is write the queries that your application will need to retrieve information

    see if the sql is simpler

    i'm guessing it will more complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    That is a REALLY bad design.

    I predict you will end up duplicating authors and/or books across several records.

    Use a many-to-many table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2008
    Posts
    4
    It's not really the time for experiments. I'm going the proven way. Though I was wondering if anyone had used this approach.

    Thank you for your time.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Its cool to experiment, but not when there is any sort of deadline looming.

    Try your schema in your spare time, and you will see what its drawbacks are and learn a lot from playing around with it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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