I have a database structure with two tables:
-
The first one is called
Author
, has two columns (AuthID
,AuthName
) and itsPrimary Key
is (PK_AuthID
). -
The second one is called
Book
and has three columns (BookID
,BookTitle
,AuthID
). ItsPrimary Key
is (PK_BookID
), and has (FK_AuthID
) defined as aForeign Key
that referes toAuthor
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?
Best Answer
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.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.