Ecto: seeking advice for table/Schema design - multiple joint tables between two entities

Questions / Help
5

As per your E-R diagram you don’t have a many-to-many relationship - those would be represented logically as a relationship with “crow’s feet” at both ends. Typically many-to-many is implemented via an associative entity (join table).

Ecto’s many_to_many is designed to work with associative entities - in effect hiding them to get back to the logical view rather than exposing the physical view.

Granted superficially Assistant and Provider look like an associative entity but as they hold information beyond the User and Organization keys they act as a full blown entity.

What you seem to be modelling is:

  • (The role of) Assistant is fulfilled by a User (Assistant belongs_to a User)
  • (The role of) Provider is fulfilled by a User (Provider belongs_to User)
  • Assistant serves an Organization (Assistant belongs_to Organization)
  • Provider serves an Organization (Provider belongs_to Organization)
  • Service is delivered by a Provider (Service belongs_to Provider)

(See step 1 (and step 1 only) of CSDP)

belongs_to, has_one, has_many and many_to_many are simply terms of the Ecto DSL - it’s not meant to accurately describe your model in terms of your domain.

Given that Organization treats Assistant and Provider as separate entities even though we could abstract them to a type of Role uniting them as a polymorphic entity, particularly in a relational model, is pre-mature given that at this point in time there is simply not enough value gained from introducing the necessary complexity (speculative design). The only evidence we have is “some fields with the same name” - reuse on its own is rarely is a good motivation for polymorphism (or inheritance).

3 Likes