Ecto, Phoenix and when EctoEnum fall short

I have two questions on ecto and phoenix.
I have an “items” table with an “item_type” which might have the value of “print” or “online” (may be using EctoEnum library)
I can make it an enum field e.g. defenum TypeEnum, print: 0, online: 1 and save the integer values in the table.
1- The question is what if the item is available as both print and online. (Perhaps save it as an array, but how?)
2- If the item is “online” I would like to save their urls. It seems that I have two options, first, having a “url” field for each and every item in the “items” table, no matter if they are print or online, or any other type, second, having another table solely dedicated to store “url”, something like a junction table.

In each scenario, I wonder what is the phoenix way of making it happen?

This is really mostly a question about database design. In answering how to solve it, concerns about Ecto are secondary.

In the case you’re describing, you’re facing a typical normalization problem. My recommendation to you in this case: Don’t over-think it! If your database design should ever become an actual problem (e. g. in terms of performance or complexity), you can always change it later.

In your particular case, I think the simplest solution would be to have two colums in your Items table:
:url, :string and is_available_for_print, :boolean.
According to your description, when there is no URL, the item is never available online. So you don’t need to store that information twice. This only leaves information about whether the item is available for print which can be stored with a simple :boolean (i. e. TINYINT) column.

1 Like

I guess you are right, that is related to database design. But still, there is a phoenix and ecto side to it. But, then again, I like your approach very much. It really is simple.
But what if I have more than two options. In this case, It also seems that I have at least two options, first creating a many-to-many relationship between types and items, second storing the data as map (postgres, jsonb). In the latter case, I am afraid it will make it more complicated, especially in filtering on the basis of this field. It is not a conundrum, of course, but I would like to know the options and how much they would cost.

1 Like