Dealing with associations

[Danwhy]

An issue that arose when implementing this into a project was 'how do we deal with associated schemas in an append only way?'.

The main problem was that associating two schemas relies on the unique primary keys. So we couldn't use our UUIDs, because there a multiple of those per table. But if we used the auto-incrementing primary key, when a record was updated the id would change, and it would no longer be associated with the record in the other table.

The answer to this was to update the associations table by appending a new record to it whenever we update either of the linked records. This means we always have the latest associations, and also the full history of all associations before this; meaning we can see exactly what version of record A was first associated with record B, which could be very useful for analytics.

One more thing to note is how we only access the latest associations when getting items from the database. We use a custom query passed to Ecto.preload:

query = from(s in schema,
          distinct: s.entry_id,
          order_by: [desc: :inserted_at],
          select: s
        )

item
|> Project.Repo.preload(query)

I'll add a full writeup of how to do this in the tutorial soon.

[nelsonic]

@Danwhy no "objection" to doing it this way.
there are a couple of other ways we can explore if the volume of data becomes too much [new records in the association table for each update], but for now, this is good because it has full referential history.

Once again, your intuition is good. 🥇