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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s