Practical use of embedded schemas for single table inheritance (STI) in Phoenix application

Questions / Help
, ,
1

TL;DR: How do you practically use the embedded schemas when implementing single table inheritance in a Phoenix application?

There are several topics on STI here and I’m pretty sure I’ve read them all, so please bare with me on this one :otter::pray:t3:

My users’ domain requires strict management of entities and their relationships. I’m therefore looking into modeling the entities part on the “entity engine” of Apache Open For Business (OfBiz).

OfBiz is an enterprise resource planning (ERP) software written in Java. Crucially, it offers a comprehensive, general, off the shelf, data model to build on. I believe that the “entity engine” part of that may, at least, offer a reasonable point of departure for my users’ needs.

Note that OfBiz’s “entity engine” includes XML-based schema definitions, and much more, that is of no interest to my use case.

Basically, there is a finite set of models and fields one could reasonably imagine when it comes to entities:

  1. natural persons and
  2. legal persons.

So, I’m creating an Entity schema tied to a corresponding table, together with embedded schemas NaturalPerson and LegalPerson following the Darren Wilson presentation at ElixirConf 2017 (see resources, references below).

The considered domain

What is particularly relevant to my application is that the domain of the industry (consultancy) centers around the concept of a Customer where:

  1. A Customer has one or more Entity:s,
  2. Each Entity may be either a NaturalPerson (a human) or, by definition, a LegalPerson (a corporation, trust etc.) and
  3. An Entity belongs to one or more Customers.

It’s inconceivable there would ever be any other types of entities than natural or legal persons.

No, I refuse to consider adding a third kind of entity anticipating the singularity!

A common use case for my users is to have “customers” in constellations such as:

  • A natural person and their fully owned corporation
  • Two natural persons
  • Two natural persons and their jointly owned corporation
  • A corporation and its subsidiaries

A new “customer” must be set up when, at some later point, constellations change. For instance, a customer doing business primarily through their fully owned corporation may sell their business but continue doing business personally.

Schemas

This is the data model.

Entity

defmodule MyApp.EntityEngine.Entity do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "entities" do
    field :name1, :string
    field :name2, :string
    field :name3, :string
    field :start_date, :date
    field :end_date, :date
    field :entity_type, Ecto.Enum, values: [:natural, :legal]

    timestamps()
  end

  @doc false
  def changeset(entity, attrs) do
    entity
    |> cast(attrs, [:name1, :name2, :name3, :start_date, :end_date, :entity_type])
    |> validate_required([:name1, :entity_type])
  end
end

NaturalPerson

defmodule MyAppp.EntityEngine.NaturalPerson do
  use Ecto.Schema
  import Ecto.Changeset

  alias MyApp.EntityEngine.Entity

  @primary_key false

  embedded_schema do
    field :first_name, :string
    field :middle_name, :string
    field :last_name, :string
    field :birth_date, :date
    field :death_date, :date

    timestamps()
  end

  @doc false
  def changeset(natural_person, attrs \\ %{}) do
    natural_person
    |> cast(attrs, [:first_name, :middle_name, :last_name, :birth_date, :death_date])
    |> validate_required([:first_name, :last_name])
  end

  def to_entity(%__MODULE__{} = natural_person) do
    %Entity{
      name1: natural_person.first_name,
      name2: natural_person.middle_name,
      name3: natural_person.last_name,
      start_date: natural_person.birth_date,
      end_date: natural_person.death_date,
      entity_type: :natural
    }
  end
end

LegalPerson

defmodule MyApp.EntityEngine.LegalPerson do
  use Ecto.Schema
  import Ecto.Changeset

  alias MyApp.EntityEngine.Entity

  @primary_key false

  embedded_schema do
    field :name, :string
    field :established_date, :date
    field :closed_date, :date

    timestamps()
  end

  @doc false
  def changeset(legal_person, attrs \\ %{}) do
    legal_person
    |> cast(attrs, [:name, :established_date, :closed_date])
    |> validate_required([:name])
  end

  def to_entity(%__MODULE__{}, legal_person) do
    %Entity{
      name1: legal_person.name,
      start_date: legal_person.established_date,
      end_date: legal_person.closed_date,
      entity_type: :legal
    }
  end
end

Practical use?

I’ve tried to approach this by adapting the generated Phoenix LiveView code – with some rather great frustrations along the way.

  • How would you approach using these schemas when creating lists, detail views and forms?

Creating a new NaturalPerson I’ve set up functions in my context like this:

  def create_natural_person(attrs \\ %{}) do
    %NaturalPerson{}
    |> NaturalPerson.changeset(attrs)
    |> Ecto.Changeset.apply_changes()
    |> NaturalPerson.to_entity()
    |> Repo.insert()
  end

The whole idea is of course to have separate form components for natural and legal persons. You would have separate changesets for these obviously. When updating an entity, you will be faced with an Entity from the database and would therefore need to “cast” this to the appropriate changeset for the embedded schema. I guess that one could run in to issues there? Then that has to go back into the database through the Entity schema again.

I guess one would set up separate routes for natural and legal persons with their own forms in live components using their respective changesets and casting from and to the Entity changeset?

Maybe you can tell that I’m stuck between the generators and just doing it myself in the best way possible.

Some pointers would be amazingly helpful here.

I feel stupid asking these questions

Hey, I’m recovering from a terrible condition that left me handicapped in many ways. I simply don’t have the energy to take things in and excel like I used to. I’m only now starting to be able to handle “stuff” and it’s actually going a lot better.

Now – this is what I’m afraid of when I post, but I know that this community is sooo amazing that my worries are unfounded :sparkles: Really, it’s different here!

I came for the BEAM but stayed for the community.

Darren Wilson’s ElixirConf 2017, regarding embedded_schema

As promised and for reference:

3 Likes
2

(apologies if this is retracing design work you’ve already done)

My first thought: is STI the right architecture for this? It seems like there are two separate things happening:

  • entity relationships, which are indifferent to person vs legal
  • forms and UI, which are specific to person vs legal

What about splitting those responsibilities into their own database tables?

Entity as the “generic” part.

  • Other records in the system hold foreign keys to Entity
  • Entity has common fields:
    • a kind column for person vs legal
    • start_date and end_date (or maybe a daterange column) for when the entity is active/valid/etc
    • a display_name column so that UI that just needs to display an overview of Entities doesn’t need to do additional queries

NaturalPerson and LegalPerson are both standard schemas, no embedded anything. They have columns like:

  • entity_id referencing back to the Entity
  • name / address / whatever fields related to the specific thing

Another thing this would make representable: name changes. You could add start/end dates to NaturalPerson etc, so that the underlying Entity remains the same even when people & companies rebrand.

Tutorials / blog posts about splitting “users” from “profiles” may be helpful here as it’s a similar transformation.

2 Likes
3

Thank you @al2o3cr!

Any work thus far is exploratory, and I’d be glad to find alternate solutions to this.

Indeed, you bring a fresh perspective to this and I appreciate that very much.

Thanks for taking the time to review and respond :pray:t3:

4

Yes, the generated code only works well for simple stuff (especially useful for gaining a sense of structure) but kind of breaks apart once you have more complex needs. I really like Saša Jurić’s Towards Maintainable Elixir series on this topic.

Basically, don’t feel pressured to do things “the Phoenix way”. Something I really like about Jurić’s approach is that the application side deals mostly with saving data with more validation going to the interface. As he says, it means more code, but it lets you focus more on business rules on the application side. I do have a sort of “trashbin” project that I run the Phoenix generators in just to get some inspiration, but I prefer to start out with a barebones LiveView that doesn’t have anything in it and work up from there.

One thing I’d investigate is the fact that you can have multiple schemas for the same table:

defmodule NaturalPerson do
  use Ecto.Schema
  schema "entities" do
    field :first_name, :string, source: :name1
    field :last_name, :string, source: :name2
    field :birth_date, :date, source: :start_date
    # ...
    common_fields() # some helper that defines common fields
  end
end

Now you do have to do a little more work when querying, because, being the same underlying table, if you do something like Repo.all(NaturalPerson) it’s going to return all entities as NaturalPersons, so you have to do something like:

Repo.all(from np in NaturalPerson, where: np.entity_type == ^:natural)

But as far as writing the code that inserts the data, you now don’t have to worry about converting it to an entity, because Ecto will handle it. (Just don’t forget that you need to add an Ecto.Changeset.change(entity, type: :natural) for your insert code)

You can also use Repo.load/2 to great effect. Rather than converting a NaturalPerson to an Entity, you can do the reverse when necessary:

entity = Repo.get!(Entity, 1)
%NaturalPerson{} = Repo.load(NaturalPerson, entity) # AFAIK this should work and convert the `:name1` field to `:first_name`

entities = Repo.all(from e in Entity, where: c.customer_id == ^customer.id)
entities = Enum.map(entities, fn
  %{entity_type: :natural} = entity -> Repo.load(NaturalPerson, entity)
  %{entity_type: :legal} = entity -> Repo.load(LegalPerson, entity)
end)

%{customer | entities: entities}

On the web interface side, you can now do things like match on the data type to display the correct LiveComponent.

You also seem a lot like me in the way that you approach problems - something that really works well for me is not doing anything with the web interface side for a particular part of the app until I’ve gotten major parts of API figured out. Doing a lot of tests even on things like Ecto is really helpful - no need to worry about remembering to type things into IEx when something crashes, just makes changes and run mix test again until you start getting what you want.

Hope this helps :slightly_smiling_face:

2 Likes
5

Thank you @voughtdq!

Yes I’ve come to appreciate this as well. I believe this is also long the way Peter Ulrich is approaching these matters, whose work I also appreciate.

This is quite an interesting idea. Many thanks!

You guessed completely right :innocent: I’ve got more things figured out, but I’ve really run into issues when trying to use the generated code for the heavy lifting. It is obviously time for me to take the training wheels off. I’m doing another iteration where I start with a generated context, but basically re-write that context to be… less “contexty” and more “Juric:y”.

Ok this is great. Again, thanks for your consideration and for elaborating on your thoughts!

Will read through Saša’s Medium articles (hadn’t seen them before so thanks for them too).

1 Like