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.