How I store Enumerations in the Database

One of the things I come across in databases now and then is a collection of single tables with a name like "MessageType". You have a look in them and it turns out to have 6 or so rows with no foreign key relationships. Every single time it turns out to be someone had the idea to store an Enumeration (Enum) type in the database. Not a bad idea as it turns out since you can add sort options, soft deletes and the like, but the implementation of a single table for each one is flawed. 

The following is how I deal with it (probably not ideal but works well for me). Essentially you define two tables in the database with names like Lookup and Value. Inside lookup you have something similar to the following. 

+------+

|id    |

|lookup|

|name  |

+------+

This is basicly a representation of the enum name. Id is usually an autoincrementing id to make joins easy while lookup is the primary key. This is the definition of the enum, IE the name part in the database, or in our example "Message". 

Then you add the enum values to your Values table which looks similar to the below, 

+---------+

|id       |

|lookupid |

|name     |

|sortorder|

|deleted  |

+---------+

Then through the power of a simple join you can get your enum values, 

SELECT * FROM Value v INNER JOIN Lookup l ON l.id = v.lookupid WHERE l.name = '?';

Read more: Search [co.de]

Posted via email from Jasper-net