4

This has been bugging me, maybe someone can help clarify.

Say I have books, and authors: one book can be written by many authors, and an author can write many books.

Why couldn't I capture this with the following schema? Why do I need a junction table?

Books
--------
BookID (PK)
AuthorID (PK)
BookName

Authors
--------
AuthorID (PK)
AuthorName
sbenderli
  • 3,654
  • 9
  • 35
  • 48

1 Answers1

6

Because in order to have a book with more than one author, you will have to create a duplicate row in the Books table. In your example this is not so bad, but imagine if the Books table had 10 columns, or 20 or more. All of that data will be duplicated, increasing the risk that data integrity will get out of whack over time.

Jason
  • 86,222
  • 15
  • 131
  • 146
  • 1
    But, strictly speaking, although my example is denormalized, and it suffers from repeating data, it is still possible to capture many-to-many relationship, is that right? – sbenderli Jun 30 '13 at 20:21
  • 2
    Yes. You can do it more than one way, and different designs will have their own pluses and minuses. – Jason Jun 30 '13 at 21:19
  • But Jason, all the books and resources I am reading about this states that to resolve many-to-many relationships, you NEED a third table. – sbenderli Jun 30 '13 at 22:55
  • 4
    I assume the books and resources you're reading are encouraging you to use best practices - which would be to use a Join table. It's not absolutely impossible to do it without one, but it's not a generally accepted good design either. – Jason Jul 01 '13 at 00:11
  • @Jason, what do you precisely mean when you say that the risk of 'data integrity' would increase? Do you ONLY mean the extra space consumed or is there something else to it? – Floatoss Feb 18 '23 at 09:29