|
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
| ID | BookTitle |
|---|
| 17 | SQL: 1999 - Understanding Relational Language Components |
| 23 | Understanding Sql's Stored Procedures: A Complete Guide to Sql/Psm |
| 45 | Database: Principles, Programming, and Performance, Second Edition |
| 65 | Lan Times Guide to Sql |
| 81 | SQL: The Complete Reference |
Authors
| ID | Author |
|---|
| 204 | Jim Melton |
| 211 | Alan R. Simon |
| 243 | Jim Gray |
| 255 | Patrick O'Neil |
| 278 | Elizabeth O'Neil |
| 280 | James R. Groff |
| 291 | Paul N. Weinberg |
BookAuthors
| BookID | AuthorID |
|---|
| 17 | 204 |
| 17 | 211 |
| 17 | 243 |
| 23 | 204 |
| 45 | 255 |
| 45 | 278 |
| 45 | 243 |
| 65 | 280 |
| 65 | 291 |
| 81 | 280 |
| 81 | 291 |
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.
|
