DB as in Off documentation, examples of the design of communication
Many-to-Many through intermediate tables.
Situation with authors and books. The book can be written by several authors, the author can write a few books.
Below I apply an example code
create table books ( Book_id Integer Not Null, Book_name Varchar (32) Not NULL, ) Create Table Autors. ( Autor_ID Integer Not Null, Autor_Name Varchar (32) Not NULL, ) Create Table Book_autor ( Book_id Integer Not Null, Autor_ID Integer Not Null, PRIMARY KEY (book_id, autor_id), Foreign Key (book_id) References Books, Foreign Key (Autor_ID) References Autors );
The question is, why in the intermediate table
PRIMARY KEY (book_id, autor_id). This leads to a ratio of 1-k-1?
Where it would be more logical
create table book_autor ( ID Integer Not Null, Book_id Integer Not Null, Autor_ID Integer Not Null, PRIMARY KEY (ID), Foreign Key (book_id) References Books, Foreign Key (Autor_ID) References Autors );
But there is no such in the documentation. How it all correctly works as an example from the documentation in the links
Answer 1, Authority 100%
Absolutely logical structure. It does not lead to one to one. Suppose we have books with ID 1, 2, 3 and authors with ID 100, 101, 102
Pramary Key will not allow insert in the book_Autor table twice recording (1,100). But it will allow you to fill it like this:
book_id autor_id 1 100. 1 102. 2 101. 2 102. 3 100. 3 101. 3 102.
Thus, we have a connection of many-to-many. Book 1 wrote the authors 100 and 102. The author 100 wrote a book 1 and 3.
And the structure with a separate column ID and PRIMARY KEY will allow us to create records:
ID Book_id autor_id 1 1100 2 1 100. 3 1100
That will probably mean that the author wrote the book 1 100 three times. Those. this table structure will not be able to us to ensure the participation of the author in writing a book only once.