OTLT (One True Lookup Table)
Posted by decipherinfosys on February 1, 2007
I have seen many organizations using a one true lookup table for look-ups. What OTLT means is that one has a table to do all the code and system related look-ups in the schema rather than having n number of look up tables. This is an OO design pushed into the database tier and poses many problems including :
a) Not being able to force foreign keys (so your data integrity is compromised right there),
b) Not being able to use the right data-types since everything has to be a string in order to accomodate all data-type values (this itself can give rise to bad data issues since a numeric(4,2) constraint cannot be enforced –> thus domain integrity is out of the door,
c) Because of (b), the length for the string column will be huge,
d) One has to then write complex SQL queries in order to retrieve the data and also take into account the type conversions that occur when you join this table with the other tables…implicit conversion is not allowed in all RDBMS and is not a good thing anyways.
When you end up with tons of look up data, this really becomes very cumbersome – though in smaller systems, this issue gets masked. One of the reasons people tend to shy away from a bunch of look up tables is because of maintenance but if you look at it, it is really not an issue and does give you a lot of benefits including the first and foremost, it preserves data integrity. If you need to have a screen to be able to modify data in those look up tables, you can create a view that covers all those look-up tables and then make that view updateable using SQL code. That way, the end users have one place to go in order to make the configuration lookup data changes and in the back-end, you are preserving data integrity and the above mentioned issues won’t arise either.
Here is an excellent article on OTLT by database guru: Joe Celko.
