SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Association Table Design Question

    I need some help with this:

    I have a Titles table and an Authors table.
    Each has an auto-incremented primary key.

    I'd like to have an association table which consists of the Title PK and the Author PK.
    n a

    I can have multiple Title entries because there may be more than one Author for a particular Title.
    Conversely, I can have a Title entry w/o an Author (NULL).

    How can I define the table w/ what amounts to multiple primary keys or only a partial key?

    Thanks in advance,

    John

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    3 tables:

    books
    -- id (pk)

    authors
    -- id (pk)

    books_to_authors
    -- books_id (fk books id)
    -- authors_id (fk authors id)

    * Books without authors would be those without a books_id inside the books_to_authors table.

  3. #3
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got it that far but what would be the primary key of the Books_to_authors table?

    You could have multiple books with different authors or you could have books with no authors (NULL).

    Thanks for taking the time to answer.

    John

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The primary key of the books_to_authors table would be books_id and authors_id.

  5. #5
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So only those books that have authors would be found.

    There's no way to find books which haven't got an author?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jbrasher View Post
    There's no way to find books which haven't got an author?
    of course there is
    Code:
    SELECT books.name
      FROM books
    LEFT OUTER 
      JOIN books_to_authors
        ON books_to_authors.books_id = books.id
     WHERE books_to_authors.books_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy,

    Here's where I'm have a problem with this.

    If I have an entry in my books_to_authors table where I have a book_id but no author_id (it's NULL) how can there be a FK on the authors tale?

    There must not be a PK for the books_to_authors table or the PK is a compound key made up of books_id and authors_id.

    In your example you have the where clause books_id IS NULL which doesn't make sense to me.

    Do I make sense? :-)

    John

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jbrasher View Post
    If I have an entry in my books_to_authors table where I have a book_id but no author_id (it's NULL) how can there be a FK on the authors tale?
    you would never have such a situation

    yes, the books-to-authors table has a PK consisting of two columns, the books_id and the authosr_id

    both of them must be NOT NULL, so the row you describe would not exist


    Quote Originally Posted by jbrasher View Post
    In your example you have the where clause books_id IS NULL which doesn't make sense to me.
    that WHERE clause goes hand in hand with the LEFT OUTER JOIN

    a LEFT OUTER JOIN, as you might know, returns NULL for any column of the right table where no row exists to match the row from the left table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I think I get it. I've been trying to 'force' the authors table to contain something that really doesn't exist there.
    The condition I'm trying to find exists between tables and not within one.

    Thanks, I suspect you'll hear from me again.

    Have a great day.

    John


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
  •