An associate has created a schema that uses an ENUM()
column for the primary key on a lookup table. The table turns a product code "FB" into it's name "Foo Bar".
This primary key is then used as a foreign key elsewhere. And at the moment, the FK is also an ENUM()
.
I think this is not a good idea. This means that to join these two tables, we end up with four lookups. The two tables, plus the two ENUM()
. Am I correct?
I'd prefer to have the FKs be CHAR(2)
to reduce the lookups. I'd also prefer that the PKs were also CHAR(2)
to reduce it completely.
The benefit of the ENUM()
s is to get constraints on the values. I wish there was something like: CHAR(2) ALLOW('FB', 'AB', 'CD')
that we could use for both the PK and FK columns.
What is:
- Best Practice
- Your preference
This concept is used elsewhere too. What if the ENUM()
's values are longer? ENUM('Ding, dong, dell', 'Baa baa black sheep')
. Now the ENUM()
is useful from a space point-of-view. Should I only care about this if there are several million rows using the values? In which case, the ENUM()
saves storage space.