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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
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.