Polymorphic association in database is when you have multiple tables that need to refer to the same table. For example, I have table users that contains details like profile information. I then have two tables, guards and operators which both have profile information. I need to link that to users table.

In Django/Rails, polymorphic association is done through this approach where you would have two fields, user_type and user_id (there are other approaches available in Django/Rails). But this breaks references in the database.

Ecto's documentation explains three ways to approach this. I would say the documentation details are a bit lacking, thus this blog post. I won't explain the first one as it is easy to understand.

Separate table for each association
@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "guards" do  
  field :position, :string
  has_one :user, {"guards_user", User}, foreign_key: :user_id

  timestamps()
end

@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "abstract table: users" do  
  field :user_id, :binary_id
  field :date_joined, :utc_datetime
  field :email, :string
  field :password, :string
  field :first_name, :string
  field :last_name, :string
  field :is_active, :boolean, default: false
  field :last_login, :utc_datetime

  timestamps()
end  

I left the schema for operators because it is pretty much the same.

One thing to note is that Ecto is not ORM, so you can create a schema that doesn't exist in the database or the schema does not have to be a one-on-one projection to the columns in the table. Take our users schema for example, we noted it as abstract table because we are not really creating users table, instead we are creating guards_user and operators_user tables with that schema.

Schema POV

From schema POV, we only have a single schema users that maps to two different tables in the database, namely guards_user and operators_user.

Database POV

We have two tables, guards_user and operators_user which have the same fields. Now, for table guards, we have Foreign Key that points to guards_user. The same goes for operators table.

                     Table "public.guards_user"
   Column    |            Type             |       Modifiers        
-------------+-----------------------------+------------------------
 user_id     | uuid                        | not null
 email       | character varying(255)      | 
 password    | character varying(255)      | 
 is_active   | boolean                     | not null default false
 date_joined | timestamp without time zone | 
 last_login  | timestamp without time zone | 
 inserted_at | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null
Indexes:  
    "guards_user_pkey" PRIMARY KEY, btree (user_id)
    "guards_user_email_index" UNIQUE, btree (email)
Referenced by:  
    TABLE "guards" CONSTRAINT "guards_user_id_fkey" FOREIGN KEY (user_id) REFERENCES guards_user(user_id)


                 Table "public.guards"
   Column    |            Type             | Modifiers 
-------------+-----------------------------+-----------
 id          | uuid                        | not null
 position    | character varying(255)      | 
 user_id     | uuid                        | 
 inserted_at | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null
Indexes:  
    "guards_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:  
    "guards_user_id_fkey" FOREIGN KEY (user_id) REFERENCES guards_user(user_id

Migration

Within migration, we have to create both guards_user and operators_user table. Note that we don't need to create users table.

def change do  
  create table(:guards, primary_key: false) do
    add :id, :binary_id, primary_key: true
    add :position, :string
    add :user_id, references(:guards_user, column: :user_id, type: :binary_id)

    timestamps()
  end
end

defmacro create_user_table(table_name) do  
  quote bind_quoted: [table_name: table_name] do
    create table(table_name, primary_key: false) do
      add :user_id, :binary_id, primary_key: true
      add :email, :string
      add :password, :string
      add :is_active, :boolean, default: false, null: false
      add :date_joined, :utc_datetime
      add :last_login, :utc_datetime

      timestamps()
    end

    create unique_index(table_name, [:email])
  end
end

def change do  
  create_user_table(:guards_user)
  create_user_table(:operators_user)
end  

Pros/Cons

Besides the cons mentioned in the documentation, pretty much we would have two tables that have the same structure. If we ever need to change the structure of users, we have to handle the changes for both tables.

In next part, we will look at the next approach using intermediary table as association. This solves the issue above.