How to handle schemas polymorphism in Phoenix?

Question

The recommended way to handle polymorphic associations in Phoenix seems to be adding an intermediate schema that contains the references to the other schemas:

• Inverse polymorphic with ecto
• https://hexdocs.pm/ecto/Ecto.Schema.html#belongs_to/3-polymorphic-associations).

So if I would like to create schemas with different kinds of animals, I would do:

defmodule Animal do
  use Ecto.Model

  schema "animals" do
    belongs_to(:dog, Dog)
    belongs_to(:cat, Cat)
    belongs_to(:owner, PetOwner)
  end
end

defmodule Dog do
  use Ecto.Model

  schema "dogs" do
  end
end

defmodule Cat do
  use Ecto.Model

  schema "cats" do
  end
end

defmodule PetOwner do
  use Ecto.Model

  schema "pet_owners" do
    has_one(:pet, Animal)
  end
end

But I could also have the PetOwner schemas containing a binary field and the type:

defmodule Dog do
  use Ecto.Model

  schema "dogs" do
  end
end

defmodule Cat do
  use Ecto.Model

  schema "cats" do
  end
end

defmodule PetOwner do
  use Ecto.Model

  schema "pet_owners" do
    field(:pet, :binary)
    field(:pet_type, :integer)
  end
end

Or even just having a nullable reference to all the animals in the owner schema:

defmodule Dog do
  use Ecto.Model

  schema "dogs" do
    belongs_to(:owner, PetOwner)
  end
end

defmodule Cat do
  use Ecto.Model

  schema "cats" do
    belongs_to(:owner, PetOwner)
  end
end

defmodule PetOwner do
  use Ecto.Model

  schema "pet_owners" do
    has_one(:cat, Cat)
    has_one(:dog, Dog)
  end
end

The first method seems to add complexity to the schemas. What are the pros and cons of the different methods?

EDIT: Let's assume that a pet owner can own only one pet, if the schema allows multiple pet, the verification is done in the changeset.

Answer 1

I spent quite a lot of time reading blog posts and answers to similar questions. I also asked this question on Elixir's discourse: https://elixirforum.com/t/how-to-handle-schemas-polymorphism-in-phoenix/13269/24 and received great answers.

A reccurent point is that this problem is more a SQL problem than a Phoenix or Ecto problem. It is true that Ecto offers some ways to solve this problem, but the resolution of this question should start with: "How to handle polymorphic association in a relational database".

Note that if you are here looking for a solution to solve "belong_to" polymorphic association (that is, if a concrete table belongs to two or more polymorphic table), there is an entire section for this in Ecto's documentation. This answer is for "has_many" polymorphic association.

Here is comparision of the different approaches wrote by ndac_todoroki in answer to my post on Elixir's forum, all credits goes to him:

Single Table Inheritance

This is when you have a big table (Animal) and each concrete animal tables are a sub-division of that table. You won’t have multiple tables.

Pros

• you will only have one table.
• fetching all animals are very easy
• it can be easily implemented in Ecto, because all concrete animal modules can have schemas different with each other but referring to the same big table.

Cons

• if you want unique fields for each types of animals, you get NULL columns everywhere (but you don’t care when using the concrete modules’ schema: it doesn’t appear.)

Class Table Inheritance

This is when you have a base table (= Animal), and each concrete animal tables have a reference to the base table plus their unique fields. (Animal {id: 1, birth: "20180101", vaccinated: true} Animal {id: 2, birth: "20111225", vaccinated: false / Cat {animal_id: 1, colour: "brown"} Snake {animal_id: 2, length: 150})

Pros

• listing all animals are quite easy, if you only need the basic informations
• no NULL columns

Cons

• while getting the full information (of an animal) from the concrete modules are quite easy (you just JOIN :animal), getting the full info from the base side is not (you cannot know what to JOIN).
• This is unless you write some kind of logic similar to polymorphic tables in Rails (animal_type and animal_id), which are mentioned “not preferable” in the Ecto docs.

Concrete Table Inheritance

No tables for Animals will be created. Each concrete animal tables will have all basic informations + their unique informations. This will be good if you want to assure that all animals are animals, but you don’t work with Animals directly. (I’d rather create a protocol named Animal rather that doing this)

Pros

• no need to JOIN when fetching a concrete animal
• works good with Ecto’s abstract tables (Animal will be an abstract table)

Cons

• you need to be careful at migrations not to break the entire inheritance
• unique assertions must be done completely on application logic
• especially ids must be unique across all animal tables, so using :binary_ids is preferred.
• any search using keys in Animal should go through all tables (ugh)

Abstract tables

This is described in the Ecto docs. (Sample repo) Using only this seems not very helpful on your usage, but if you are looking at STI or CTI, it may help the implementation.

When you create a Class Table Inheritance, each table refers to an Animal base table. Using abstract tables, you can split that into multiple base tables for each concrete animal tables, for example a cat table will refer tocat_base and snake to snake_base, where cat_base and snake_base will have same columns. Then we will create an abstract table animal, and when you cat |> Animal.add_base_animal_info() it will create a cat_base.

Pros

• you can retrieve concrete animal from Animals

Cons

• you need to JOIN every concrete *_base tables to do list_animals

I think this is somewhere between Class Table Inheritance and Concrete Table Inheritance.

Embedding data

Embedding can be done in Postgres and MongoDB etc. You can have a single animal table with a field that accepts a map (field :details, :map). Then you define many concrete animal modules which its schema refers to that animal table, having a embeds_one :details, CatDetails where you define a embed_schema for CatDetails. (This example is STI with Embeds)

Pros

• tables will be very clean
• no NULLs
• listing all animals is easy
• storing data using the concrete animals’ schema will validate the shape of maps you pass

Cons

• the shape of the maps inside :details cannot be verified in DB level
• searching for fields inside embeds may not be good (depends on what db you use)