0

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)
7
  • Some of your questions you'll find the answers to by using foreign keys.
    – J.D.
    Dec 7, 2021 at 3:22
  • @J.D. In the association table, I used foreign keys for the book id and the tag id. One thing is, what if there are no more associations for a certain tag and it remains intact in the tags table? How do I approach this? Do I create a trigger that checks if that tag still has associations? Dec 7, 2021 at 6:05
  • 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? 1st step - try to insert tags, one-by-one, ignoring duplication errors. After this all tags in array are present in a table. 2nd step - insert ebook (check for dup too). 3rd step - insert (ebook, tag) pairs into adjacency table. Of course, create needed unique constraints and foreign keys (with needed cascade actions - and you do not need in triggers).
    – Akina
    Dec 7, 2021 at 6:35
  • @Akina So this works for inserts. What about deletes? My main concern is having a tag with no association in the adjacency table. I do not want that. How do I deal with that? Dec 7, 2021 at 6:44
  • The purpose of the foreign keys (with cascade actions) is when you delete the parent associated entity, it will automatically delete the child as well, i.e. the TagsToEbooks entity. So it wouldn't be possible to have an orphaned record in TagsToEbooks, like you're concerned about.
    – J.D.
    Dec 7, 2021 at 12:38

1 Answer 1

1

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;

DEMO fiddle

2
  • Would you be so kind to explain the code further. I saw the demo and that is what I want but I want to make sure I understand the implementation. Thank you. Dec 7, 2021 at 17:07
  • @mariofares Please ask specific questions. What exactly is unclear?
    – Akina
    Dec 7, 2021 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.