How to implement insert, update, delete with a many-to-many relationship for tags?



  • 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)



  • The implementation may be:

    -- books table
    CREATE TABLE Ebook (ebook_id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name VARCHAR(255) UNIQUE);
    

    -- tags table
    CREATE TABLE Tags (tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) UNIQUE);

    -- adjacency table
    PRAGMA foreign_keys = ON;
    CREATE TABLE TagsToEbooks (ebook_id INT,
    tag_id INT,
    PRIMARY KEY (ebook_id, tag_id),
    FOREIGN KEY (ebook_id) REFERENCES Ebook (ebook_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES Tags (tag_id)
    ON DELETE CASCADE ON UPDATE CASCADE);

    -- Trigger which will delete unused tags after book deletion
    CREATE TRIGGER delete_unused_tags
    AFTER DELETE ON Ebook
    BEGIN
    DELETE
    FROM Tags
    WHERE NOT EXISTS ( SELECT NULL
    FROM TagsToEbooks
    WHERE Tags.tag_id = TagsToEbooks.tag_id);
    END;

    https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=3240437678fe642477e75f07a30affb6




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2