A one to many relationship between two tables should be implemented with two or three tables? For example should we have:
author(id,otherAttributtes)
books(id,authorid,otherAttributes)
or
author(id,otherAttributtes)
books(id,otherAttributes)
authorConnectsBooks(authorid,booksid)
I like more the first approach but i have seen the second and in more complicated applications a lot of times. Is there any downside for the first method, or it's just personal which way to follow?
The first example shows a one to many relationship, while the second shows a many to many relationship.
Example lets say we use the first example
Author
AuthorID
Book
BookID
AuthorID
How would you represent that both Jane and Jon wrote the book "Stackoverflow for fun"? In this relationship table you cannot, you have expressed that one author can write many books. So either Jane wrote it or Jon wrote it. If only one of them wrote the books you could use this relationship type. However, if you want to show that both wrote this book you need a many to many relationship.
Now using this same analogy of Jane and Jon you can represent both authors to this one book using your second example - many to many relationship.
Authors
Joel
Jeff
Books
Stackoverflow Joel
Poor Jeff, he is not credited with stackoverflow from the above example...so we need to fix that:
Author
Joel
Jeff
Books
Stackoverflow
AuthorBooks
Stackoverflow Jeff
Stackoverflow Joel
Now everyone's happy...
A one-to-many relationship should be implemented with 2 tables.
But the relationship you propose in your example (between Authors and Books) is not one-to-many, is many-to-many.
"An Author can write many Books, and a Book can be written by one or more Authors."
And a many-to-many relationship should be implemented with 3 tables.
Have a good day.
one to many is two tables.
the second one is many to many.
Authors
1 Larry Niven
2 Jerry Pournelle
Books
1 Integral Trees
2 King David's Spaceship
3 The Mote in God's eye
AuthorsBooks
1 1
2 2
1 3
2 3
If the relationship is actually one to many, there is no need for a linking table (authorConnectsBooks in your example). In your example, however, you don't have a one-to-many relationship since one author can write many books and one book can be written by many authors. In your example, you actually have a many-to-many relationship. If you actually have a many-to-many relationship, then you do need a linking table (authorConnectsBooks in your example).
As everyone stated out, the first one is a one to many relationship in which you dont need the extra table. Just two tables should work. But in the second case, since its a many to many realtionship, you'll need to add an extra table known as the Junction or the cross-reference table because most database management systems only support one-to-many relationships, it is necessary to implement such relationships manually via a third junction table. The junction table's primary key is normally formed using the primary keys of the tables it connects. Here is a wiki page that explains the exact same example that you asked:
