Books and authors, many-to-many

A question in two parts! I am attempting to design a database for a library catalogue with a fairly simple search...

strategy. I was wondering how you overcome the problem of a many to many relationship, e.g. Book Titles and Authors. Books can have many authors and authors can write more than one book. Do you have to create another entity with authorID and booktitleID? My second question is, can an entity have more than one foreign key? Help!!

Yes, a third table, the BookAuthors table, which I call an intersection or relationship table, is required.

Consider the following books:

SQL: 1999 - Understanding Relational Language Components

by Jim Melton, Alan R. Simon, Jim Gray

Understanding Sql's Stored Procedures: A Complete Guide to Sql/Psm

by Jim Melton

Database: Principles, Programming, and Performance, Second Edition

by Patrick O'Neil, Elizabeth O'Neil, Jim Gray

Lan Times Guide to Sql

by James R. Groff, Paul N. Weinberg

SQL: The Complete Reference

by James R. Groff, Paul N. Weinberg

If these books were stored in the database, they might look like this:

Books

Authors

BookAuthors

For this design, you will most likely require INNER JOIN queries only, unless you're looking for books that don't have an author or authors that don't have a book, which you might do occasionally to ensure that the contents of the tables have been entered properly.

As for your second question, yes, an entity can definitely have more than one foreign key. The BookAuthors table has two. BookID is a foreign key to the ID column in Books, and AuthorID is a foreign key to the ID column in Authors. The database will not get confused when you declare these foreign keys, because you have to specify which table the particular ID belongs to.

You didn't ask, but there's also the question of primary keys. What should the primary key of BookAuthors be? My recommendation is to make the pair of columns, BookID and AuthorID, the primary key for BookAuthors -- i.e., a compound primary key. This will ensure that you don't enter the same author for the same book more than once.