if im creating a relation should i store a text value or a unique id for reference?
ID’s don’t typically change. Text values can.
If a value can potentially change (for example spelling or slight modification to the text value), then relations can break when the value is changed.
Take a person’s name for example. If you use a name, you not only risk multiple people having the same name, but you also have a bit of a mess if someone gets married and changes their name. If you use an ID, that ID will always stay the same no matter what.
3 Likes
I totally agree with Jeff as a general rule, and about unique IDs in particular.
However, I have found that sometimes it’s worth the risk of things changing to relate on text values instead of IDs. When the choices are limited, like a pick list for example, it saves lookup columns, keeps things a little simpler to work with because the data tables show the actual value, and I suspect it might be faster. It’s also handy when integrating using Zapier or Make because you can just send a value like “Completed” without having to find and use the ID for that choice.
In the rare cases I have decided to change a value, it’s possible to update an entire column at once using copy-paste.
I would never try to relate to a text column that is typed in by users, only ones that use a choice component or some other way to limit the possibilities.
1 Like
I always use UUID (row ID). However, at times I have to also redundantly add basic column to my child table due to GBT limitations in case relation→lookup from parent (or vise versa) fails.
3 Likes
I only create relations based on row IDs.
Row IDs can be difficult to read and hard to work with in visibility and filtering conditions. In some cases in can be helpful to create an “Human-readable Row ID” which will be absolutely unique and that you will never edit. I learnt this from Mark at @V88.