Let’s say I have 3 tables: users
, profiles
and skills
.
Resulting in following associations:
- User
has_one
Profile (:profile
) and Profilebelongs_to
User (:user
). - User
has_many
Skill (:skills
) and Skillbelongs_to
User (:user)
.
My search feature will show users whith user.name
, or profile.introduction
, or having one or more skills matching the search term.
My problem here is the one-to-many
realtionship between users
and skills
.
I’m thinking to create a denormaized table or a view just for this search feature.
For example skill_documents
with user_id
and tsv
column (to store the concatenation of all skills names of a given user) . This way everytime an user adds, removes or updates a skill the tsv
column will be updated accordingly.
Please do you think this is an acceptable solution or what would you suggest me instead?
Thanks
Edit: I’m not finding the general programming
category so I put my question in the members only
one.
Edit 2: So as suggested by @dimitarvp I moved my question to Elixir
category and added Ecto
tags. There is no Ecto
dedicated category apparently.