Frameworks are well and good, but sometimes they shield us from simple concepts that we, as developers, should know. Let me tell you a story about this theme, and what happened.
I was handed a really simple task: I had to create two entities (Store and Brand). A Store could have had many brands attached, so it was a simple many-to-many relationship, with a junction table. Something like:
create table stores( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) NOT NULL ) DEFAULT CHARACTER SET utf8, ENGINE = INNODB; create table brands( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) NOT NULL ) DEFAULT CHARACTER SET utf8, ENGINE = INNODB; create table store_brand ( store_id INT , brand_id INT, PRIMARY KEY (store_id, brand_id), FOREIGN KEY store_fk (store_id) REFERENCES stores(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY brand_fk (brand_id) REFERENCES brands(id) ON UPDATE CASCADE ON DELETE CASCADE ) DEFAULT CHARACTER SET utf8, ENGINE = INNODB; INSERT INTO brands(name) VALUES ('Scitenoa'), ('Drirathiel'), ('Trelod'), ('Jiofrax'), ('Chacaka'), INSERT INTO stores(name) VALUES ('Castrealm'); INSERT INTO store_brand VALUES (1,1), (1,2), (1,3);
The brands were fixed, so I had to create a simple CRUD interface only for the stores. Insertion and deletion were a breeze to code. When I came to write the code for the Update part, I stopped: “How should I write it?”
When you update a many-to-many relation you could not call a simple
UPDATE statement on the junction table, you have to:
- Insert all the newly associated entities,
- Remove all the entities that are no more part of the relation.
Solution 1: Delete all the old tuples and insert
The first solution was very trivial: remove all the rows from the junction table related to a given store, and insert the new rows.
DELETE FROM store_brand WHERE store_id=1; INSERT INTO store_brand VALUES (1,1), (1,4), (1,5);
This would have done well, but the fact that I was deleting and inserting a set of rows that could potentially differ only for one item was disturbing me. So I went on to search a new solution.
Solution 2: insert only new tuples and drop the ones that were not inserted
I was looking for a way to insert only new rows: the ignore clause from MySQL was good for this. Then I had to drop all the rows that were not in the set of those just added. In code:
INSERT IGNORE INTO store_brand VALUES (1,1), (1,2), (1,3); DELETE FROM store_brand WHERE brand_id NOT IN (1,2,3) AND store_id=1;
So that’s was it! Sometimes is good to be looking at these low-level details, that often are hidden behind super-optimized libraries.
Cover image by Scott Robinson