Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

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.


This was last published in August 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close