Elixir Ecto - Association with 3 or more tables

Question

if I have profile, item, location table which:

• profile has many location.
• item has one location.
• location in profile and item will NOT overlap.

How should I design the location table? Is below valid? location belongs to 2 tables?

schema "profiles" do
...
has_many :location, Location
end

schema "items" do
...
has_one :location, Location
end

schema "locations" do
...
belongs_to :profile, Profile
belongs_to :item, Item
end

or should there be 2 location tables?

Answer 1

This is a very subjective question and depends on how you are going to use the data from the locations table. This is generally the recommended approach for polymorphic associations in Ecto with an additional database constraint that forces that one of profile_id or item_id is always set on a location record.

defmodule MyApp.Repo.Migrations.CreateLocations do
  use Ecto.Migration

  def change do
    create table(:locations) do
      add :profile_id, references(:profiles, on_delete: :delete_all)
      add :item_id, references(:items, on_delete: :delete_all)
    end

    create constraint(:locations, :at_least_one_ref, check: "profile_id is not null or item_id is not null")
    create index(:locations, [:profile_id, :item_id])
  end
end

And then in the changeset, you can validate this constraint:

def changeset(location, attrs) do 
  location
  |> cast(attrs, [:profile_id, :item_id])
  |> check_constraint(:profile_id, name: :at_least_one_ref)
end

Another way which is pretty specific to Ecto, but works really well, especially since you mentioned that there are no location overlap between profile and item is using embedded associations using embeds_one and embeds_many. If you are using Postgres, you should even be able to create indexes on the JSONB column if you need to.