On Tue, 10 Apr 2018 11:24:49 +0100
hmidi slim <hmidi.slim2@gmail.com> wrote:
> Hi,
> Is it a good thing to use enum type such a column in a table instead of
> making a foreign key which references to another table?
> I found these links talking about enum and when I will use them:
> http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
> https://www.percona.com/blog/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/
>
> Is there any advices or new updates to use enum type in order to decrease
> the number of join between the table and optimize the performance and query
> runtime?
Performance isn't always the only issue.
Enums work well if you're 100% sure that the values will never change.
While it's not impossible to change them, it's essentially a schema change,
which can be tricky to do on an active databse, due to locking.
A foreign table has the advantage of being easy to change, but with the
extra join required to get the text representation.
A foreign table with a text field doesn't require the join, but takes up
more space and requires a cascading change if you need to change an enum
value. Adding new values is pretty easy, though.
Another option is a text field with a check constraint to ensure the data
in it stays valid. This is somewhere in between as changing the check constraint
is easier than with an enum, but harder than with a foriegn table. It doesn't
require a join to get the text representation of the value, but takes up more
space (depending on the lenght of the text for each value).
So you have to balance the requirements of your use case to decide what method
is best.
--
Bill Moran