Alternative to a many to many relationship

Question

Let's say I have a two tables - book and author. A book can have many authors and an author can have many books, which means they are in a many to many relationship and I'll need a third table to implement that.

I really want to avoid this, as I am a beginner and this makes the queries more complicated for me.

So my question is : what would be the best way to implement this type of relationship excluding many-to-many?

I was thinking just to put a foreign key in each table so I have an authoridFK in my books table and bookIdFK in my authors table. But I am not sure what will type of a relationship would that be, will it me correct and how it will look on an ERD?

Can someone clarify these to me?

As a beginner, why don't you try to implement it correctly and learn along the way? The correct way would be the implementation of an Intersection Entity within your database, as you originally noted. The other table definitions that you are suggesting would make your life harder when you need to add further relational data. — Brendan Lesniak, Dec 07 '16 at 21:39
The *correct* way is the one you're trying to avoid. Three tables: Authors, Books, and a relationship table that links the two. — Tyler Roper, Dec 07 '16 at 21:40
Thanks for the replies. You are all right, but I am wondering is this the only way to do this. Let's say I want to denormilize from 3rd to 2nd form. How would I implement this then? — Robert Ross, Dec 07 '16 at 21:43
Don't denormalise. Don't do it the *wrong* way. There is nothing good about that. — trincot, Dec 07 '16 at 21:44
By de-normalizing within the database, you are going to end up with repeated data, it's almost unavoidable. That's the point of storing data in 3rd normal form, so you don't need repeated instances of data. The ONLY instance I see denormalization being OK is in certain Data Rollups or instances where the data needs to be pre-computed or calculated, such as for search tables. — Brendan Lesniak, Dec 07 '16 at 21:45
If you wanted to use two tables, there is no possible way to avoid duplicate data. Either you'd have to repeat the author information for each and every book they write, changing only the `BookID` column, or you'd have to repeat book information for each author, changing only the `AuthorID`. What if you wanted to change the author's name at some point down the road though? Instead of updating one row, you could potentially have to update hundreds. With the proper method (using a third table), all you'd have to update is *one* row. — Tyler Roper, Dec 07 '16 at 21:46
Just throwing this out there, the keyword you might be looking for is `junction table` http://stackoverflow.com/a/14676389/2333499 but now that I google more, `Intersection Entity` or `Associative Entity` appear to be more proper terms. — SqlZim, Dec 07 '16 at 22:00

score 4 · Answer 1 · edited Dec 07 '16 at 22:01

The 'best', and most standard way is to use a third table to maintain the relationship.

As for query complexity, you can either look into using JOIN in your queries or getting an ORM which handles these type of relationships for you.

An example join query for the given context which will return a list of all authors and all books they have contributed to would look like this:

select a.AuthorName, b.BookName 
from author a
  join AuthorBookMapping m on m.AuthorID = a.AuthorID
  join Book b on m.BookID = b.BookID

score 0 · Answer 2 · answered Nov 19 '20 at 15:36

The intermediate table is the only correct way to build your relation. This logic does not complicate the structure, but on the opposite, it allows to comply with good practices:

each data item must be located in a unique place in the database
each record (object) must have a unique primary key.

This way you can both link authors to their books, possibly assign several authors to the same book, and of course several books to the same author.

Advanced CRUD systems also allow you to display the author(s) in the book list and edit these relationships.

Here's a tutorial about relationships in a MySQL database

It explains the main things about direct / indirect relationships, and identifying / non-identifying relationships

Alternative to a many to many relationship

2 Answers2