Using enum where sensible

On database design here.

Having been schooled in the merits of normalization previously, I had always thought it sensible to use separate tables for dictionary fields. 2NF, 3NF, yes?

Now I have a [members] table where they have some title (Dr, Mr, Mrs, ..). I used to put those in a separate table, so when you retrieve data, you gotta do a join on the identifier… yes. Horror of horrors.

Table joins simply don’t scale as well even though you do indexing and index hinting, so use enum where sensible.

[members].[title] of type enum (‘Dr’,’Mr’,’Mrs’, ..). In my next post I’ll put out a MySQL enum helper for Code Igniter (CI). Given some table, the function returns a list of rows that are of enum data type, nicely separated out.

I’m currently using it with my CI_Smarty class. If you don’t want to learn Smarty syntax, feel free to use the default solution, it works fine as well.