2

I have a database structure with two tables:

  • The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID).

  • The second one is called Book and has three columns (BookID, BookTitle, AuthID). Its Primary Key is (PK_BookID), and has (FK_AuthID) defined as a Foreign Key that referes to Author table.

The question is: If one Book has more than one Author, what is the best design pattern to follow in order to maintain the second Author, and at the same time keep the Book table without NULL values?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Ashraf Abusada
  • 151
  • 1
  • 8

2 Answers2

4

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex.

BookAuthors(BookID (FK points to Book), AuthorID (FK points to Author))

In this case, you can remove the AuthID column from the Book table. Don't bother abbreviating to AuthID - you don't really think that makes you more productive or makes the code easier to read, do you?

Also, as a general rule, don't spend time trying to worry about how you will eliminate NULLs everywhere.

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
-1

AuthID does not belong in the Book table at all because a book can have more than one author. My suggestion:

Author (AuthID*, AuthName)
Book (BookID*, BookTitle)
BookAuthor (BookID*,AuthID*)

*=key attributes(s).

No nulls required.

nvogel
  • 3,807
  • 21
  • 25