I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my tables but since I am inexperienced, I can't exactly figure out how an insert, update, delete would look like.
Ebook Table: - id - name Tags Table: - id - name TagsToEbooks - ebook_id - tag_id
I am working with C++ and Sqlite. Let's say I have a C++ function that takes an array of tags and I want to associate them with a certain ebook. How would I implement or write it? How should I use placeholders? Do I iterate over the array and run a SQL statement for each single tag? Moreover, if I am deleting all tags associated with an ebook, they might remain in the tags table with no associations in the linking table(that has ebook_id and tag_id).
Please teach me how to cover these scenarios.
Some thoughts I had using pseudocode:
// When I want to insert a books tags insert or select id if it exists then insert association // When I want to update a books tags delete associations of book then do the insert function // When I want to delete a books tags DELETE FROM tasglink WHERE book_id in (SELECT id FROM ebooks WHERE name = :name) //Create a trigger After delete on tagslink DELETE FROM tags WHERE id NOT IN (SELECT id FROM tagslink WHERE GROUP BY id)