Normalization of library book table with multiple authors

Question

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?

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

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.

edited Apr 13 '17 at 12:42

Community

1

answered Apr 29 '15 at 17:03

Aaron Bertrand

180,303
28
400
614

I was trying my best to avoid using nulls, as the database will be used to store relatively large data that will be accessed online, I don't want it to slow down for any reason (nulls) is one of that reasons. – Ashraf Abusada Apr 29 '15 at 17:10
3

@Ashraf Where did you get the idea that NULLs slow things down? – Aaron Bertrand Apr 29 '15 at 17:12
I learned it from a programmer who teaches database programming. – Ashraf Abusada Apr 29 '15 at 17:17
@Ashraf Maybe you should consider other opinions or, you know, proving that it slows anything down. – Aaron Bertrand Apr 29 '15 at 17:20
He told me that he did an experiment, and proven that it slows database performance. – Ashraf Abusada Apr 29 '15 at 17:23
3

@Ashraf You know a NULL in a database once set my dog on fire. – Aaron Bertrand Apr 29 '15 at 17:25

score -1 · Answer 2 · answered Apr 29 '15 at 20:33

-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.

answered Apr 29 '15 at 20:33

nvogel

3,777
20
25

Hm, but that's just what the other answer suggests. What is your point in repeating the same suggestion? – Andriy M Apr 30 '15 at 05:28
1

I agree but I didn't quite understand your concern about nulls so I wanted to emphasize the point that no nulls are needed. – nvogel Apr 30 '15 at 07:56
1

All right. I didn't make any point in this thread apart from my previous comment, but that's okay. :) Of course, you could just post a comment under the other answer to point out this aspect to the OP, but fine. Personally I'm not in the habit of downvoting duplicate answers, but some people might be (just FYI). – Andriy M Apr 30 '15 at 08:05

Normalization of library book table with multiple authors

2 Answers2