Return to Question

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 IN (SELECT id FROM tagslink WHERE id = :id)

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)

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)

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 IN (SELECT id FROM tagslink WHERE id = :id)

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.

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)