jdumont
SQL structure for many, different child entities
I’m back with another SQL / database structure question. I feel it’s related to polymorphism, STi and all those concepts, but seems inverted in my case (one parent type, many children; rather than many parent types to one child).
A simplified example — not the one I’m actually working with — would be the concept of a Document entity owning multiple different content types. Let’s say, Video, Image, Markdown, etc where each has a different structure and fields, but share an index field, so that all of the different content types for a given Document can be loaded and then displayed in order. Is this polymorphism?
Would something like an intermediate entity (Content for arguments sake) with an exclusive arc for the different content types and foreign key for the document be a suitable solution?
Given I’m using PostgreSQL and it has better support for querying embedded fields, would I be better off just dumping a JSON array into a field on the Document?
EDITED TO ADD: Just thinking through the problem a little more, simply embedding an array of maps/JSON won’t work, as within those maps I’ll want to reference records in another table.
Most Liked
peerreynders
That’s the recommended approach for Ecto
https://media.pragprog.com/titles/wmecto/code/priv/repo/migrations/20180620125250_add_notes_tables.exs
https://media.pragprog.com/titles/wmecto/code/lib/music_db/note.ex
So something like
[Document] <-> [Content] <-> [Video] | [Image] | [Markup]
id <-- document_id
video_id --> id
image_id --> id
markup_id --> id
In the above Video, Image, and Markup can be referenced by multiple documents. If they strictly belong to one document maybe this would be more appropriate.
[Document] <-> [Content] <-> [Video] | [Image] | [Markup]
id <-- document_id
id <-- content_id
id <-- content_id
id <-- content_id
Given
I don’t think it makes sense to try to normalize Video, Image, Markup in to some normalized, uniform structure - so sticking with an association with Content probably makes sense.
jdumont
I think that unless I find any issues after looking into the required queries further; this is the solution I’ll go with. I’ll also revisit my recursive structure in the CrossFit app and see whether this would simplify it — I suspect it would.
Thanks for all your help @peerreynders & @OvermindDL1
I’ll be sure to post up a gist of this solution in full once I’ve built it so that it can help others.
dimitarvp
Naively asking before thinking about it deeper – have you considered trying PostgreSQL’s table inheritance?
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Catch errors, track performance, monitor hosts and more.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"