Foreign key strategy in Ecto for has_ relationships of ALog schemas?

[njwest]

I wonder what the most efficient x practical strategy for foreign key functionality is for a traditional Owner has_one Item relationship in an Append-only Log scheme with Ecto.

Would it be to go with the distributed system method, and use UUID across every related row with no explicit foreign keys? e.g., Owner.entry_id === Item.entry_id for the Item owned by that Owner, with no :owner_entry_id or references() in Item's migration?

Or would it be worth saving space by going with a foreign key of id (e.g., owner_id) on an Item, and when querying the association, selecting an Item's Owner by owner_id then selecting the newest entry with that owner's entry_id?

I see in the migrations for this repo, a standard references() is used:

    create table(:items) do
      add(:name, :string)
      add(:entry_id, :string)
      add(:deleted, :boolean, default: false)
      add(:owner, references(:users))

      timestamps()
    end

Interested in any thoughts about this in the context of the current UUID implementation of Alog

[njwest]

Current ALog Foreign Key Implementation

Testing out the code in this repo's alog module, rows associated with a newly appended Record are updated with new foreign keys corresponding to the new Record.

Am about to test this with associated records in tables that are also implemented as append-only logs

Pure Append-only Method

A purely append-only answer to this question on the other dwyl alog example repo: dwyl/phoenix-ecto-append-only-log-example#6

The linked answer in short (to my understanding): After appending an updated Record, load all associated rows of that Record and append new copies of said rows with the newly appended Record's record_id as a foreign key

This method gives us the most history, but will also consume the most disk space

UUID-only Method

If we do not implement foreign keys, and we replace foreign_key_ids on records with associations to append-only tables with the UUIDs of their parent records, we lose out on Ecto's association optimizations (and potentially decrease the value of utilities such as the Elixir implementation of DataLoader), but we do not have to insert or update new rows for associated records each time we update a record

This may be a practical solution for the 5% of cases an app's records are updated frequently + with a lot of associations in environments in which DBs can't handle a lot of writes/updates