In my application one can register an organization, and users within an organization. A user can also be part of different organizations. But there are different kind of users among an organization such as providers, assistants, …
I could simply have a joint table between organization and users and have a field in the users table letting me know what type of user it is (“assistant”/“provider”); however these different users have a set of fields specific to them. That would lead to many columns with null values. Also, for example providers have other links to other entities.
Here is then how I’d ideally conceptualize the relations in the database:
An organization has many providers and an organization may have many assistants as well.
A provider and an assistant have many common fields (e.g. account-specific fields such as email/password/…) which I’d put in the common users table,
and have a set of different fields as well, which I’d put in the joint tables (assistants/providers).
A user may be part of many organizations.
A provider has also a relation to other entities; for example a provider has a set of services he provides.
In Ecto, I would have a schema User, Provider, Assistant, Organization and Service.
Even though these seem many_to_many relations, I guess I can’t work with many_to_many relations in Ecto schemas in this case, but rather will work only with has_many and belongs_to. However, it’s weird to write in my Ecto schema: a user “has many” providers; a user “has many” assistants; it’s very misleading. It’s rather: a user IS many providers; a user IS many assistants.
This will also result in a user structure being an assistant or a provider (these will be lists as a user may be multiple providers for different companies); this is known by checking if the lists user.assistants or user.providers is empty. It’s not very readable I guess.
Thank you for any advice.
Note that I’m really a beginner with Ecto/Elixir yet.
But if I use many_to_many relations, I won’t get access to the data within the joint tables (I mean the resulting structs won’t contain the data from the joint tables)?
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 Providerlook 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)
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).
