Bidirectional relationships and polymorphic associations in Ecto

Question

I have a table named :issues and I need to establish a way to link two issues. I have thought of creating a new table called :links, but I'm having some trouble designing the Ecto schema because of the rules I must follow:

1. Links should be two-way and be any of these types: ["blocked-by" | "blocking" | "relates-to"]
2. If Issue-A is blocked-by Issue-B, Issue-B should be blocking Issue-A
3. If Issue-A relates-to Issue-B, Issue-B should relates-to Issue-A 
4. Creating a link on one issue should create a link on the linked issue, following rules 1 and 2.

What further complicates this is the existence of other tables :stories, :notes, :milestones because I should be able to link a record from a table with a record from another table or from the same table with the same rules above.

I've also thought of creating a table for every type of link. For example, the updated schema for Issue would become like this:

defmodule MyApplication.Issue do
  use Ecto.Schema

  @primary_key false
  schema "issues" do
    field(:issue_id, Ecto.UUID, primary_key: true)
    has_many(:linked_issues, LinkedIssue)
    has_many(:linked_stories, LinkedStory)
    has_many(:linked_notes, LinkedNote)
    has_many(:linked_milestones, LinkedMilestone)
  end
end

and I have to do the same thing for Story, Note, and Milestone. I'm just confused as to how I would set up my migrations and references. I'm also not sure how the join tables would know which table is linking them, and how I would make the relationship two-way following the fourth rule above.

Answer 1

Bidirectional links could be modeled as simple as 2 unidirectional links, for example. To enforce the data integrity in this case you will have to rely either on the app level logic or database triggers (the latter is more bullet-proof but comes with a higher maintenance cost).

The polymorphism you need here is a bit trickier problem. Ecto doesn't support polymorphic associations in the way Rails' ActiveRecord (and similar frameworks) does. Intentionally, because it comes with a price: broken FK constraints (and weaker data integrity as the result) and some performance penalty (which might be neglectable at first glance but hurts as the database grow).

There are at least 3 solutions that Ecto "suggests" instead:

1. Single links table, multiple foreign keys.
2. Multiple <object>_links tables with the so-called abstract schema for Link itself
3. Single links table with multiple many-to-many join tables (issues_links, milestones_links, ...)

Each comes with multiple advantages and disadvantages, some querying specific etc.

It is briefly explained here https://hexdocs.pm/ecto/Ecto.Schema.html#belongs_to/3-polymorphic-associations but I would also suggest referring to https://pragprog.com/book/wmecto/programming-ecto - this book contains the whole chapter (14) with a detailed explanation for the issue and all the possible solutions mentioned above. It would be unfair to just copy-paste it here pretending to be my own knowledge :)