I’m trying to define an Ecto table schema that includes a column that is used to uniquely identify rows but is NOT the primary key. Think of a short coupon code.
Although I could use PostGres’ UUID or GUID feature, I would prefer to use a shorter key for 2 reasons:
- They take up less memory. A smaller string requires less memory.
- It is extremely inconvenient to rely on huge UUID or GUID keys – think of the pain they will cause customer service who must refer to each record with those long-winded strings.
I’m not seeing any information in https://hexdocs.pm/ecto/Ecto.Schema.html that explains how to add a key to column, let alone something that demonstrates how to set up a custom mutator that would let me generate a unique value for a column. Can anyone let me know what I missed? Thanks!
You’re going to want to make sure that you
create unique_index(:code_table, [:code]) in your migration, and then you just need to make sure that when you create an
Ecto.Changeset, you have something that will put a generated code if it doesn’t already exist.
@halostatue has answered your first question. Now as to your other question – about the keys. I’ve had success with the custom_base library. It generates code for a custom BaseN encoder/decoder.
defmodule MyBase do
use CustomBase, ~c(0123456789abcdefghijklmnopqrstuvwxyz) # <-- you can customize these chars
# notice it is NOT a string!
The nice thing about this one is that you can continue to use integer keys (so actually you wouldn’t have to change much). When looking up the coupon code, you’d just decode it.
In this example, our coupon code is “jjp” which would come from the row id
There might be concern about someone finding this pattern, so you could use a different order for the characters:
Or, if these coupon codes will be spoken over the phone, you might remove certain characters that can be misheard (i.e. b, c, d, e, p, t, v, and z are easy to mishear because they have the
/iː/ sound at the end).
Of course keeping in mind that you can shuffle the characters to avoid any exploitation/prediction of the next coupon code in a sequence.
Finally - you have to be careful with this method. You will have to maintain it for the life of the youngest coupon code generated using this method.