Several authors for one book in SQL

Question

I have a books table and on each row (book) some books have several authors.

Using SQLite I can do this CREATE TABLE users (authors text[])

How can I insert several values to authors ?

score 0 · Answer 1 · answered Mar 10 '19 at 21:51

There are two approaches, one being "right" and the second being more of a kludge.

If you create several tables

Author
  AuthorID (long)
  AuthorName (string)

Book
  BookID (Long)
  BookName (string)

BookAuthors
  BookAuthorID (long)
  BookID (long / external key)
  AuthorID (long / external key)

The other approach is list the authors as one long string with some special character (such as the | symbol) to separate them. This approach is much more difficult to search however.

score 0 · Accepted Answer · answered Mar 10 '19 at 21:57

0

What we are looking at here is what is known as a "one to many" relation see [ link ]. The idea is that you create the books and then for each author you could link to a book in a table book-authors. Then you join the tables.

answered Mar 10 '19 at 21:57

Albin wärme

251
1
14

This one looks good. can you please tell me how to insert to it ? – phranku Mar 10 '19 at 23:03
You can not insert to multiple tables in one **statement** but you can do it in one transaction. It could look something like this. INSERT INTO books (book_name, release_date) VALUES ('my_book', 2019); SET @last_id_in_table1 = LAST_INSERT_ID(); INSERT INTO book_authors (book_id, author_id) VALUES (@last_id_in_table1, any_author_id); INSERT INTO book_authors (book_id, author_id) VALUES (@last_id_in_table1, any_other_author_id); – Albin wärme Mar 11 '19 at 16:55

Several authors for one book in SQL

2 Answers2