Many to Many polymorphism


Polymorphic relations in databases are a way to use dynamic weak FKs to avoid having to create too many tables.

Why is the FK dynamic?
Because it can point to different tables
Why is it weak?
Because we can't confirm it exists in the related table or use cascading (without application code)

Let's use an example to show the difference between the polymorphic way and the standard way to build a many to many relation.

In this example we have

  • Users
  • Posts
  • Images

We want an Image to be able to be connected to multiple Users & Posts (Many to Many) And here's how that would look using standard many to many relations

Default setup

Here's what the same solution would look like if we used Polymorphism. Poly setup

This looks complicated but let me explain.

  • imageable_id is a FK to our related table, it's either an id value in
    the Users table or an id value in the Posts table.
  • imageable_type tells us which table to look for the imageable_id in.
    The imageable_type is either Posts or Users.

For example if we have an Imageable row with the values
imageable_id = 1
imageable_type = Users
imageable_id = 5

This means the Image with id 1 is connected to the User with id 5.

In the initial DB example it's not obvious that polymorphism is the better choice,
but consider if we addded Videos and they also should have Images.
With the standard set up we would need to create both a Videos table and an Images_Videos table.

With Polymorphism we would only need to create the videos table.

Polymorphism can be tricky to grasp, to get a good feel for it I suggest creating a test database like the example above and playing around with values.

The next challenge is to not overuse your recently learned pattern, and how to do that is for you to figure out.