Friday, January 15, 2010

Why would you need a Gender table in your DB??

A friend of mine was tweeting about how he found a drop down control in some application he’s working on that binds on a (“lookup”) table with only the 2 rows for male & female.. Check the short conversation on twitter.

In my humble opinion -as weird it may sound-  this approach (for similar, small lookups) might makes sense, in some way. Not exactly as is, may be it will make sense more if it’s within a lookup module (a single table for all lookups where every entry has a lookup type).

Other “regular” approaches (& their expected problems) :

People usually tackle such small lookups using enums, or just using an “agreed on” key/value pairs convention (not stores anywhere in code or DB, may be only a code comment here or there) which usually tend to get forgotten & you have to  trace code to know which key stands for each lookup value..

Even worse, is using a boolean for such

Benefits of a DB Table approach:

Other than the benefits of avoiding the above, using DB to explicitly state lookup values, is that they are more dynamic, ie: you can always edit them without touching much code, that would be handy in cases like:

  • Clients constantly changing small stuff like spelling, etc
  • Adding other functionalities/features, like multilingual support for eg..

Thus, I find avoiding enums, etc & using tables even for small stuff like that gender much more convenient..

Let me know what you think about it, & (hopefully) suggest a better approach..


Mohamed Atia said...

Well, I find this approach very broad, and I think for small problems like this one enum would do it. If I used for each Data field like this a new table, like gender, martial status ..., it will be a mess in the SQL statements writing, Joins just need more concentration, but in more complicated situations I prefer your approach, as you said it will give you the chance to edit afterward some of the properties.

Nice blog btw :Da

Mohamed Gamal El-Din said...

Agree with M.Atia

Shady M. Najib said...

I kinda agree.. Yet, you forgot that on most enterprise applications, such changes always happen..

Also concerning SQL-joins & performance, etc; there're ways to optimize either through view, procedures, etc (SQL engines are smart enough to compile similar stuff to run faster) or even through hardware :)

Thanks for taking the time to read it :)


All the opinions expressed on this blog are my own and don't necessarily represent my employer's positions, strategies or opinions.