Polymorphism and Inheritance within a Phoenix Application in 2022

dvdclrk

Good afternoon all,

So I’m building a “small” web app to track books read, shows and movies watched, comics enjoyed, etc. But I’ve hit a bit of “devblock” in that I’m not sure how to I should properly map out the tables.

Initially, I wanted to do a single “Items” table that would hold a primary binary_id key and a type and a few other common data points which would lead you to some distinct tables for each type. But then as I was reading the Ecto docs I stumbled on the polymorphism section about how that’s discouraged for a number of good reasons.

Then I figured I’d do Single Table Inheritance and have an “Items” table with all the columns needed but that would lead to a lot of nulls bouncing around which I wasn’t a fan of. I could use schemas to hide them away so maybe this is the right way but I wanted to explore other options as well.

My current favorite is the separate tables for each “Item” type and then an “Items” abstract table. There are only a very few times in the initial design of my application where “Items” is used so I don’t believe the DB would be hampered by having to join the distinct tables too often.

But perhaps utilizing embedded schemata is the way to go? I’ve seen the polymorphic embed package.

The relevant post where I got these ideas is linked here. It’s a fairly exhaustive list of pros and cons but I was wondering if anyone else has gone through this process and had any wisdom on mapping inheritance in databases. I may just have to settle on one option and test it to see if it fits my needs but was hoping someone had something they could add.

Thanks for any insight in advance.

3 comments

/phoenix #ecto #polymorphism

0 1198 3

2022-07-14 02:13:01 UTC

Showing Posts 1 to 3

Show Best Posts
Show All (oldest first)
Show All (newest first)

lud

My take on this would be the following:

If it is a commercial product where you want to enforce data consistency, then you will need different tables and a bunch of schemas, because you do not want nulls everywhere.

If it is a self hosted tool, or a learning app, then just put everything in a single table, and move on. Nobody cares if some book misses some data because you missclicked and created a movie before realizing it was a book you wanted to store. You actually don’t even need a database for that, a file would be enough.

Post #1

al2o3cr

Some other things to think about:

do you need relationships between items that’s enforced by the database?
do you need to query for items that might be of mixed types?
do you need user-defined attributes? (“shelf”, “box #” etc)

Especially in that latter case, you might consider a less “schema-y” approach like EAV - store an “item” and then have a single “attributes” table.

In that case, the difference between a “movie” and a “book” is the type and the initial set of possible attributes

Post #2

mathieuprog

My quick take:
If your polymorphic data contains relations (foreign keys) that you want to join on, then use tables. Otherwise, use JSONB with polymorphic_embed library.
EAV is generally not recommended if you can use one of the two above.

Post #3

— All posts loaded —