I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here.
I have 3 tables (details left out for clarity) :
id bigserial (PK) sku character varying(24) name character varying(32) ...
id bigserial (PK) name cahracter varying(32) ...
item_group_id bigint (FK -> inv_item_groups) item_id bigint (FK -> inv_items)
Now, in my code, I have an object like so (in pseudo-code)
class ItemGroup id:long groupName:String items:long
and these objects can be modified, then needs to be updated. Since I want to preserve the key integrity, I need the
inv_item_group_members table (otherwise, I would've used other solutions).
Now, the usual way I was doing this was to
DELETE FROM inv_item_group_members WHERE item_group_id = $1 -- where $1 is the object's id
the, for each
items in the object
INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)
Is there a better solution? What are the alternative? I'm thinking of a SQL function, but not really sure what's the best approach here (I'm not very experienced with PGSQL, yet.) I have read about writable CTE, but it does not address the case when elements are removed from the array (i.e. association removed).