Association Table Design Question

Databases
1

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

3 tables:

books
– id i[/i]

authors
– id i[/i]

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

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

The primary key of the books_to_authors table would be books_id and authors_id.

5

So only those books that have authors would be found.

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

6

of course there is

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

:cool:

7

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? :slight_smile:

John

8

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

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

9

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