Free MMC Webcast
This Wednesday, March 9th
Do you use Source Control for your database objects? If you are just starting, or looking for tips on how to optimize your source control, this webcast will provide you insight on how to accomplish this non-intuitive task for relational databases. There will be lots of other topics covered as well, centered on how to be productive using Microsoft Management Console. Click here to register now!
Presented by our own Stephen Wynkoop
New SelecTViews Show Available Free Now
Open source databases as a tool for expanding your systems, Interview with EnterpriseDB CEO, Ed Boyajian, a SQL Server tip of the day…Also, information about tablets, Jeopardy! challenge, new Gartner BI report and much more.
[Watch the Show]
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)
One True Lookup Table
While reading an article by Joe Celko I came across a term he thought was credited to Paul Keister called "One True Lookup Table". The term I had never heard before, but the concept was not new. In fact, I had even used this technique in some of my earliest database designs a couple decades ago.
The concept of "One True Lookup Table" (OTLT) is to place all your lookup values into a single table instead of creating different lookup tables for different entities. The primary reasons for doing this are:
- One set of code to maintain lookups for the entire database.
- Easy to distribute updates because only one table is modified.
Don’t Do This!
- You can’t enforce referential integrity.
- You end up with multiple joins on the same table in many queries (not necessarily a bad thing but not for this purpose)
- If you really enforce the data domain through Check Constraints, the constraint logic becomes incredibly complicated.
- Simply because data has the same structure doesn’t mean it is the same table,..you end up with a composite key (Entity, Key) when there really isn’t one
All of the arguments (and there were many) in the feedback that followed was pure laziness or a lack of good development tools to manage lots of lookup tables.
So, I propose the question, "How do you manage dozens, even hundreds of lookup tables?" If you have a lot of databases you support, or distribute your database to lots of customers, you may even have literally thousands of code tables to maintain. What techniques have you found for keeping things up to date, and still have time to do the rest of your job. Send your ideas and experiences to btaylor@sswug.org.
Cheers,
Ben