Hi !!
When a many to many relationship is required, the linking table is the only way to go or does any other option exists?

Suppose the following scenario

One author - One book (90 %)
One author - many books (8 %)
Many authors - One book (2 %)

Could the following design be an option or is it totally wrong?

the linking table is the best way to go

your diagram doesn't really convey the idea of a working many-to-many scenario

perhaps you could illustrate how it works by showing us a few rows of data from each table

Authors and books are not the entities in the real scenario but my english is not good enough to describe exactly the real scenario.

Here are the rows for authors table


and for books table


In the real scenario the entityB (books) will be autogenerated by EntityA (Authors). This will happen only once. So having bookID in the Authors table will restrict author of collaborating more than once but in my case this is not a problem. The entities A (authors) already exists in the table. My programm is going to insert mostly (98%) entity B rows in the Books table. The rest 2 % of entities B that are result of collaboration are going to modify the EntityA (Authors) Table.

it is an incomplete many-to-many relationship, specially crippled for your particular needs -- an author can collaborate only once

i would nevertheless still use a proper relationship table

what you should do is write the queries that your application will need to retrieve information

see if the sql is simpler

i'm guessing it will more complex

That is a REALLY bad design.

I predict you will end up duplicating authors and/or books across several records.

Use a many-to-many table.

It's not really the time for experiments. I'm going the proven way. Though I was wondering if anyone had used this approach.

Thank you for your time.

Its cool to experiment, but not when there is any sort of deadline looming.

Try your schema in your spare time, and you will see what its drawbacks are and learn a lot from playing around with it.