Hellow. It seems to me that I just don't understand something quite obvios in databases.
So, we have an author that write books and have books themselves. One author can write many books as well as one book could be written by many authors.
Thus, we have two tables 'Books' and 'Authors'.
In 'Authors' I have an 'ID'(Primary key) and 'Name', for example:
1 - L.Carrol
2 - D.Brown
In 'Books' - 'ID' (pr.key), 'Name' and 'Authors' (and this column is foreign key to the 'Authors' table ID)
1 - Some_name - 2 (L.Carol)
2 - Another_name - 2,1 (D.Brown, L.Carol)
And here is my stumbling block, cause i don't understand how to provide the possibility to choose several values from 'Authors' table to one column in 'Books' table.But this must be so simple, isn't it? I've red about many-to-many relationship, saw many examples with added extra table to implement that, but still don't understand how to store multiple values from one table in the other's table column. Please, explain the logic, how should I do something like that ? I use SQLiteStudio but clear sql is appropriate too. Help ^(
You should have third intermediate table which will have following columns:
This way you will be able to create a record which will map 1 author to 1 book. So you can have following records:
AuthorXID and BookXID - foreign keys from corresponding tables.
So Book2 has 2 authors, Author1 has 2 books.
Also separate tables for Books and Authors don't need to contain any info about anything except itself.
Authors .. 1---Many .. BOOKSFORAUTHORS .. Many---1 .. Books