Relational database design

The design of a relational database is usually managed in several stages. The first stage is to identify the relational schema for the tables.

To clarify the process, consider the design of a small database of personally owned books. Here is a reasonable schema:

Authors(id, lastName, firstName, yob)
Books(title, edition, hardcover, publisher, pubYear, isbn, numPages)
Publishers(id, name, city, country, url)

Most of the fields specified here are text type (String, in Java). The Authors.yob (for year of birth), Books.pubYear, and Books.numPages fields should be integer type. The primary keys are Authors.id, Books.isbn, and Publishers.id. A publisher's ID would be a short string, such as A-W for Addison-Wesley, or Wiley for John Wiley and Sons.

The next step is to identify the foreign key constraints. One is obvious: Books.publisher references Publishers.id.

But we can't use foreign keys to link the Authors and Books tables because their relationship is many-to-many...