jdumont

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

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

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

dimitarvp

Naively asking before thinking about it deeper – have you considered trying PostgreSQL’s table inheritance?

Where Next?

Popular in Questions Top

sen
Hi All, I set a environment variables in dev.exs , like below code. when i start server, how can i set the ${enable} value? thanks. d...
New
fireproofsocks
I’m working on defining a simple Ecto schema for a table (in PostGres), but I don’t see where I can define a column as NOT NULL. Conside...
New
fireproofsocks
Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? Ecto.Repo — Ecto v3.14.0 has exampl...
New
LegitStack
I’m trying to make a websocket server in Phoenix or raw Elixir. I heard about gun, I think I could use cowboy, but since I’m not that sma...
New
dokuzbir
I want to highlight html closing tags when i click a html tag. That works in .html files but doesnt work for html.eex templates. How can...
New
alice
Hey, Just curious what are the main benefits of Elixir compared to Clojure? When is Elixir more useful than Clojure and vice versa? Th...
New
sergio_101
I am VERY much an elixir newbie. I have taken one elixir course and one phoenix course on Udemy. During that course, I saw the instructor...
New
fayddelight
I tried installing elixir 1.11.2 erlang 23.3.4 via asdf in my zsh shell. Enabled the versions locally and globally. When I list them ...
New
jason.o
In the code below, if the create action is not set to accept “extra_key” as an input, it errors out with a message shown above. Is there ...
New
joaquinalcerro
Hi there, I am working with Ecto-Postgresql and I need to call all of the records from a specific table but the table has 40,000 records...
New

Other popular topics Top

danschultzer
None of the current solutions worked well for me, so I went ahead and built a user management system from scratch. This project took far...
548 29377 241
New
Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
mcarvalho
What is the difference between System.get_env and Application.get_env? For example, what are best practices to use one versus another.
New
JeremM34
Hello, how can I check the Phoenix version ? Thanks !
New
chrismccord
Phoenix 1.4.0 released Phoenix 1.4 is out! This release ships with exciting new features, most notably with HTTP2 support, improved deve...
688 30877 112
New
minhajuddin
I have seen a lot of code which picks the first element from a list using Enum.at(0) instead of List.first. Is there a reason why people ...
New
msaraiva
Surface is an experimental library built on top of Phoenix LiveView and its new LiveComponent API that aims to provide a more declarative...
564 43622 214
New
hariharasudhan94
lets say i have a sample like a = 20; b = 10; if (a &gt; b) do {:ok, "a"} end if (a &lt; b) do {:ok, b} end if (a == b) do {:ok, "equa...
New
gausby
I asked this very same question on twitter and got some interesting feedback, but I thought it would be a good question to ask here as we...
1207 39297 209
New
hariharasudhan94
Lets say I have map like this fetching from my database %{"_id" =&gt; #BSON.ObjectId&lt;58eb1a7a9ad169198c3dXXXX&gt;, "email" =&gt; ...
New

We're in Beta

About us Mission Statement