So I have this tables setup where I’m receiving foreign data and need to insert it into my database.
These elements I receive can have a URL attached to them but may not. So I have a urls
table which has foreign keys to a url_domains
table and url_paths
table. There is, of course, also the table for storing the elements received from the third party called elements
.
To tie these both together, there’s a mapping table with a bigserial primary key and two columns with foreign keys to urls
and elements
; this addresses the fact that I may only have been given a URL or just an element with no URL or have an element which has a URL; all three cases can still get saved and indexed. For simplicity, let’s call this table mappings
.
This is more for my edification of how to use Ecto so let’s assume that this setup works, is sane, and is the right way to go.
My question is…what is the best means of inserting data into this? There can be cases where the URL was received before the element so, in those cases, I need to update the row in mappings
that has the URL of the element I just received to also now point to the right row in elements
.
I can, obviously, insert each thing individually and manually check whether it already exists and perform updates to mappings
when I find that something already exists and need to link it to the new element but this feels both verbose and inefficient.
Ideally, I’d like to receive the data, be able to formulate it into a single call that inserts the element, URL, or both (depending on what was received) with a new row in mappings
that has proper references and, in cases where the element’s URL is already in mappings
for the element I’ve just received, have things be smart enough to insert the element data into elements
and then update the mappings
row with the corresponding URL to our new elements
row.
Is this feasible? Or is the complexity of the situation necessitate just manually handling each element and checking if any already exist and doing the updates myself? I know Ecto can do Upserts so, theoretically, this could be cleanly done but I’m having a hard time thinking about how that code might look.