I'm working on a project where I need a book database. The problem is as follows:
A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to different books in different roles.
Book1 PersonA (author), PersonB(editor) Book2 PersonC (author), PersonB(translator), PersonD(editor) Book3 PersonE (author), PersonA(editor)
I have a
(bookID, bookTitle), a
(personID, personName) and a
(roleID, roleName). Then I need a way to relate these tables and then query them. I thought of a junction table that has the following row structure:
bookId personID roleID
Is this the optimal way of handling this problem (when it comes to insertion, update and deletion of entries on all these tables)? If not, what are the alternatives?
Note: I'm using SQLite3.
PS: I hope my use of the terminology is correct.