0

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 id and 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
);
  • Both variants are possible. Your question is not technique, it is political... – Akina Sep 9 '18 at 19:31
  • I understand that. I'm curious if there any hidden benefits of keeping both an id and a code . – IM_AG Sep 9 '18 at 19:45
  • 1
    There are systems in which each record MUST have UUID (as a state which have sense - unique within the whole database). If not, I cannot find ever tiny reason for UUID existence. – Akina Sep 9 '18 at 20:01
  • @Akina, mucho gracias. – IM_AG Sep 9 '18 at 20:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.