Handle polymorphic model

I’m creating a Phoenix App in which I have users than could be either Recruiters, HeadHunters, or Admins.
They share a lot of characteristics : they all have a first name, last name, email address, password, etc. And as you can guess they also have a lot differencies.

I am not sure how to handle this in Phoenix with contexts and my DB; should I have three tables and duplicates the common fields ? My guess would be to have one user table/model and three different contexts (Recruiter / HeadHunters / Admins ) But I am not sure this is the way the Phoenix philosophy works.

What is the best architecture to handle this kind of polymorphic table ?

1 Like

This doesn’t sound very polymorphic to me due to several common fields.

I’d go for a single record – say User – and then add metadata field (of the Postgres jsonb type which translates to Map in Elixir) that holds the parts that are different. Combined with a type field in the User model, it should work just fine.

2 Likes

Hi @dimitarvp thank you very much for your help !

Yeah sorry my explanation was wrong, I was actually referring to single table inheritance coming from the rails world.

I saw people using things such as embeds_one (or embeds_many), do you think that’d be a good solution too ?

Otherwise your solution make sense to me also :slight_smile:

Sure, it absolutely would work. I am just saying that on a quick glance I don’t see the need for it. I usually go for a single table with a type and metadata columns and found it to be more forgiving for long-term maintenance.

But don’t get influenced by a stranger. If f.ex. the fields that are different between all your different types of users are the majority then the polymorphic approach would make sense.

2 Likes

In somewhat the opposite direction from @dimitarvp’s solution, consider extracting these common pieces into a separate entity that Recruiter, HeadHunter and Admin all reference. You might call it User or Profile.

Compared to single-table inheritance, this approach uses a different relationship between the two things:

  • Recruiter is a User (single-table inheritance)
  • Recruiter has a Profile

Splitting out Profile lets your system provide common operations that Recruiter, HeadHunter etc all need - for instance, password reset flows - as operations on Profile.

One key factor to consider is how the other entities in your system will relate to Recruiter etc - will the relationships be "only a Recruiter" or will they need to point to any of Recruiter / HeadHunter / etc?

  • kind-specific relationships ("only a Recruiter")
    • a good fit for the Profile setup, as related entities can use foreign keys to specific kinds
    • tricky to enforce with constraints in single-table inheritance
  • generic relationships ("any of Recruiter / HeadHunter / Admin")
    • can work if entities instead store a reference to the Profile
    • straightforward with single-table inheritance
1 Like

I personally would use one table with three schemata in the backend and three schemata in the controllers for user input. The schemas can have virtual fields (or embedded schemata if you’re feeling more adventurous) which match specific details which you can stash in a jsonb.

You shouldn’t do this if you need a lot of searching on the non-common fields that are in jsonb. You also shouldn’t do this if you need joins across the different types of users to model a real-world relationship between humans.

2 Likes

The Ecto docs have a really good, in-depth read about some options here.

3 Likes

One key factor to consider is how the other entities in your system will relate to Recruiter etc - will the relationships be "only a Recruiter " or will they need to point to any of Recruiter / HeadHunter / etc?

  • kind-specific relationships ("only a Recruiter ")
    • a good fit for the Profile setup, as related entities can use foreign keys to specific kinds
    • tricky to enforce with constraints in single-table inheritance
  • generic relationships ("any of Recruiter / HeadHunter / Admin ")
    • can work if entities instead store a reference to the Profile
    • straightforward with single-table inheritance

thank you very much for your answer @al2o3cr :slight_smile:

Well, most of the time other entities will refer to specificaly to either a Recruiter / HeadHunter / Admin. So yeah, I guess it makes senses to have a User table to store common characteristics such as password management, email, etc. and expose it via an Account context for example.

But it terms of design, I am not sure how I should build the Profile part of the architecture : should I have 3 tables RecruiterProfile / HeadHunterProfile / AdminProfile, and expose them via three different contexts (Recruiter / HeadHunter / Admin) ? or one single Profile context ?

The App will have a very different logic when either a recruiter / headhunter / admin is using it.

If these differents fields are not supposed to be queried a lot and are more like a bag of extra specific data, check out the library that I just published, which brings support for polymorphic embeds :point_down:

Using embeds will be the easiest solution.

If however these fields are more than just a bag of extra data, and need to be queried, or that each have specific associations (joins), etc., follow @al2o3cr recommendation.

Never tried @ityonemo’s recommendation though. But one single table will lead to a lot of null values and I’d first check the other mentioned approaches.

1 Like

You won’t get any null values if you do it right because each schema will have a code-enforced limited view of the table that puts blinders around the data that are null. The struct that the schema generates won’t have fields from the table that don’t apply to it. Or you could use jsonb.

2 Likes