What about a simple table with `varchar` primary key (used as foreign keys) instead of enums?

Chat / Discussions
, , , ,
1

Hi,

I am hesitating between different solutions for a (maybe not so fixed in the future) enum field in my (postgres) database and ecto schema.

I have read several posts about this, and I quite like the solution described in Using Enums in PostgreSQL with Ecto. However I just stumbled on this old postgresql mailing list post and the technique seems appealing as it is also flexible, safer at the database level and avoids joins:

Otherwise a simple table with a primary key of type ‘text’ that is
used as a foreign key in the other table, so I can change/alter the
valid values later. No join needed! Remember that PK/FK do not always
have to be of type ‘serial’.

What do you think about it? Also does anyone use this technique and can share its experience using it (with ecto) compared to other ones?

2

Well, what will you store in this varchar column? String representations of your enums?

Then, what happens when you change the name of one of those enum fields in your application? What happens if you load a string that is none of the possible enum values in your application?

And besides this, a varchar-column takes significantly lot more space than a single TINYINT column (which, by itself, can already store more different enum values than you’d ever need)

3

In this case I would store string representations indeed.

I have not tested but from my comprehension of the first blog post linked, it seems that both techniques could be combined:

  • using ecto_enum at the ecto schema level to profit from its features
  • using the foreign key column (pointing to the table with only the varchar primary key) at the migration level to prevent inconsistent values in the database if the application is changed

This would favor fast queries (no join, not as fast as a TINYINT though), safety and controlled flexibility over storage space.

This would force me to create a new primary key, then update all records that use the previous foreign key to use the new one, then remove the old one. Depending on the needs, this could be a good thing compared to using a string a the database representation of the ecto_enum type. The alternative would be to use CHECK constraints, or a real postgres enum type at the cost of flexibility, or a joined table.

I have to test what happens in this case when using ecto_enum with a string representation of the enum at the database level. I guess I would expect an exception.

Edit: I am most likely overthinking this. As I don’t know yet if these enum fields will become dynamic later (which is my issue in one case), I should most likely go with another table and use join instead, because renaming them would be a major pain as you noted.