I have a table that represents a list of currencies. Here is a simplified version of it:
CREATE TABLE currencies ( id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY, code varchar(3) NOT NULL UNIQUE, name varchar(255) NOT NULL, precision integer NOT NULL );
As you can see, I have an auto-generated
id field of type UUID, but then I also have a UNIQUE
code field. Are there any benefits of keeping both
code fields? I have an idea to drop
code and use
id as holder for code values, since I need uniqueness for all the currency codes anyways. So the new table would like this
CREATE TABLE currencies ( id varchar(3) NOT NULL PRIMARY KEY, name varchar(255) NOT NULL, precision integer NOT NULL );
Seeing as your question appears to hinge on preference, any answer seems necessarily subjective. Given Akina’s point that some systems require UUID, it might be useful to have such a field in place if you foresee such an eventuality.
If you’re at all concerned with the cumulative effects of your choice on your storage needs, then you’d likely want to stick with your
varchar(3) value, seeing as it will consume around 4 bytes, whereas a
UUID value will clock in closer to 128 bits (16 bytes).
That being said, I don’t imagine you would need to worry about that unless you’re working with exceptionally large amounts of data, which appears unlikely seeing as you’re limiting the length of the varchar id to three characters, which caps your id column at 50,653 entries, provided you stick to alphanumeric values which use the English alphabet.
Beyond all that, you may want to use only one unique identifier so as to avoid any possible confusion from having two columns that essentially serve the same purpose.