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.