SelecTViews
Hosted by Stephen Wynkoop
Security policy gotchas, social media and data ownership questions, working with unstructured data…also on the show today, Couchbase with information about the merger plus the SQL Server tip of the day.
Watch the Show
$$SWYNK$$
Featured Article(s)
Non-Tech Blunders (Part 1 of 3)
Along the lines of Admistrative and Development Blunders there are other bluders that we as DBA’s can make. This session is going to cover ten of these that we as DBA’s make. We will talk about examples and walk through how we can avoid these in the future.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)
OTLT Reloaded
The concept of OTLT (One True Lookup Table) where you put all of your lookup data (Key Value Pairs) into a single table to simplify your database/application design.
The primary complaints of using separate tables are as follows:
- I have to write the same interface over and over to manage or consume data from these different tables
- It is easier to maintain on table than many, perhaps even hundreds of tables for the purpose of application simplicity
- Distributing modifications is complicated by each additional table, and instance of database using it
Even if I don’t personally agree with your perspective, I sincerely appreciate your honest responses on this, (or any topic). Here are some thoughts that come to mind reflecting on how I would solve the issues raised.
I don’t recall any comments that pure normalization was a bad design choice. The comments were that the additional work generated wasn’t worth the cost.
For managing and consuming the data from multiple lookup tables, the Chain of Responsibility Pattern seems to be a good design choice. Using a OTLT design you have to have a column that filters out the desired entity. Often this will be a string or an Enum.
Using Chain of Responsibility, instead of calling a single procedure and filtering, you will instead use an object oriented method to instantiate a different class (generally auto generated by a decent ORM tool) substituting the correct one through Chain of Responsibility implementation.
In my experience this is cake, no extra development work, accurately manages the scope in a way that a string cannot, and allows the database to work in the way it was designed to be most efficient.
The second advantage is that I don’t have a third column in my lookup table against which I have to index in order to get the necessary performance.
A third advantage is that I can design a controller utilizing composition that will perform the filtering and sorting functions often used with lookup data consumers, and take this load off my database server; always a good choice.
A fourth advantage is that I can easily introduce caching of lookup data through early load, lazy load or other techniques.
Tomorrow I’ll have some reflection on the issue of maintaining and distribution of change to lookup data.
Following are Some Comments from our Readers…and again, thank you all for taking the time to write in.
Charlie:
Is this an early April Fools joke?
If you really want to have fun, why not write a follow-up about the OTRD (One True Reference Dimension) for OLAP.
That way your data warehouse would be one source of the one truth! 🙂
Scott:
Think of it this way, “single point of failure”, or “bottleneck”. I too have been known to utilize OTLT, but that was back before I had apps of scale to support. As a DBA, it my job to keep the pipes clear and the engine running well. OTLT runs contrary to that goal.
Clint:
I just wanted to put my 2 cents in on OTLT…..
I am very much for this approach. I use OTLT in every application I am a part of developing. I would agree with normalization just to normalize is not the way to go.
What you are possibly overlooking in this approach is that is not just a data base issue it saves my team countless hours in coding on the front end as well. Once you transition to a OTLT you can create a user control (drop down button in my case) that all you pass in is your virtual table name and you have a very sophisticated lookup table available drag and drop. All the filter and sort options are built in.
I have taken the concept too far you don’t have to force every lookup table into this model but if you get 80% of them you are saving mucho time. I have tried sticking states in this model and wasn’t a big fan of how it turned out. Which is what I am saying about certain lookups deserve / require a dedicated lookup table. Others don’t. Don’t judge it until you try it.