SelecTViews (Free video show!)
So much data, so many databases…to the moon! Cloud exit strategies, cloud security considerations, wireless access to data… Jeopardy!(tm) ….and the SSWUG Virtual Conference, Bus. Intelligence Upgrade hesitations and much more.
[Watch the Show]
Featured Article(s)
Troubleshooting SQL Server 2008 Database Mail Problems
In this article, Alexander Chigrik explains some problems that you can have when you work with SQL Server 2008 Database Mail. He also tells you how you can resolve these problems.
Featured White Paper(s)
Essential Performance Tools for SQL Server DBAS
Optimizing SQL Server performance can be a daunting task. Especially so for an increasing number of reluctant DBAs faced with… (read more)
OTLT – One True Lookup Table
Well, we certainly have some debate around the concept of the OTLT concept presented in the Newsletter last Monday. I received a lot of feedback. I’m surprised at how much of the feedback was in favor of the concept.
One thing I have observed over the years is that when responses begin to become passionate that the full scope of the problem isn’t known. I think as I read responses from readers that the issue for OTLT is the sheer amount of work that is generated in databases having a large number of lookup tables.
OTLT works nice in a Type/SubType logical database diagram by formalizing the fact that a large number of lookup tables are nothing more than key value pairs, ID or Code and Description.
But translating this into a physical database design makes database modeling a nightmare. You end up with an un-readable diagram with most of your tables pointing to this one table. The diagram becomes un-readable. Ok, diagrams are not our goal in software development…the goal is to create database designs that are accurate and maintainable. But if you can’t diagram a database in a way that is readable, does it not increase the complexity for maintenance?
In some ways I like the Enum datatype found in MySql for this very reason. It allows you to store the key and retrieve the value of the key without a separate lookup table in your schema.
It still seems to me that we are missing some kind of tool necessary to maintain lookup information. This is something we have had to solve for decades now. Regardless of a single OTLT or multiple lookup tables, we still continue to write code to maintain, manage and distribute this data. It’s a common enough problem in every database I have ever seen…seems like there is an opportunity there for some kind of product.
That’s enough from me. Here are some responses from all of you.
Mike:
Did you check out the IQ (Irony Quotient) in Joe’s piece? This is one of the crazier ideas I’ve seen (not quite on a par with naming all your variables with numbers to improve security, but getting there!).
I tend to separate the lookup tables out into a separate schema so that they’re apart from all the "normal" data tables. This also makes it easy to ensure that only one person has write-access to them, thus avoiding lots of problems. Not entirely perfect (e.g. when the Keeper goes on holidays or is sick) but it seems to work very well.
Chris:
…I refuse to have a brazillion trivial tables for Eye Color and (edited @#$%^&*) like that. For most of my purposes, sticking that (edited @#$%^&) into one reference type table, with cols appropriate for making "virtual tables" if needed/desired, valid from/to cols, and the usual kind of stuff. Who cares about multiple joins into a reference table? That’s what table aliases are for.
Normalization for the sake of normalization is dumb, imo. I have yet to run into substantial problems using a reference table in this manner – no matter what purist kewlkidz say.
Jag:
I look forward to your findings, our database suffers from the effects of OTLT!!! I am trying to get it re-factored in seperate tables, but the idea of hundreds of tables is putting the architects off.
Sloan:
You can read my mini take on it here:
http://granadacoder.wordpress.com/2009/05/06/one-true-lookup-table-discussion/
I address this issue "You can’t enforce referential integrity"
Well, there is certainly a lot to think about. Get into the discussion. Send your concerns, solutions, or comments to btaylor@sswug.org.
Cheers,
Ben