Menu
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?
350
In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders. In this example the primary key field in the Customers table, Customer ID, is designed to contain unique values.
Example. If the two entity types are 'Customer' and 'Account,' each 'Customer' can have many 'Accounts,' but each 'Account' can only be owned by one 'Customer. ' In this case, we can say that each customer is linked to a number of accounts. As a result, the relationship is one-to-many.
A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship.
So, what is one-to-many relationship in SQL? A one-to-many relationship occurs when one record in table 1 is related to one or more records in table 2. However, one record in table 2 cannot be related to more than one record in table 1.
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...
175
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.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
