This article is about smart database design. I have been programming in SQL server for more than 8 years. I love the design and analysis phase of a project where you create new tables, views etc... The success of any software project depends on its initial design. Once a poorly designed database hits production, no one will dare to make changes to it!(and if the manager who was in charge is still around, he is not going to agree that it was a badly designed database)
Ok, coming to the point, I am going to talk about Reference tables or Look Up tables. Any database will have some of these, and few database have LOT of lookup tables. In worst cases there are columns in tables with values like 0,1,2,3, etc and with no foreign key reference. You just have to look at the stored procedures which reference these tables and see how these numeric values are interpreted.
I have always used just one lookup table. This works most of the time for me. Let's see some examples of tables which are dependent on LookUp table. Lets see the two tables, Employee and Job from Pubs database:
Figure 1 - Employee Table
Figure 2 - Job Table
In the above example, “Employee” table has a column “Job_ID” with integer values. The “Jobs” table has the Job description, usually tables like “Jobs” do not have more than 10 or 15 or at the most 50 records.
Example 2
Figure 3 - Customer table
Figure 4 - State Table
In the above example “state” column in the “Customer” table references “StateCode” column in the “State” table to get the “StateName”.
So, there may be several tables in our databases which needs a look up table. For a database with more than 100 tables, there may be atleast 10-20 look up tables, or even more if there are multiple columns in one table which references multiple look up tables.
I came up with a “Generic” lookup table which can be used in most of these scenarios.
Figure 5
From the above data , every distinct category would have replaced a table. Now we do not need “Job” and “State” tables. This is a very simple way to handle multiple small tables and keep the information in one place. Any kind “hardcoding” in stored procedures can be avoided by using this table.
Conclusion
Advantages of "GenericLookUp" Table
1.We can store integer values to most of the data and have the description stored in the "GenericLookUp" table. As we know that performance is always going to be better when we deal with numeric columns than non numeric columns.
2.Easy maintainabilty. Lets say we have 20 LookUp tables in a application. With this "GenericLookUp" table, we just need 3 stored pros(Insert/Update/Delete) to maintain the data.
3.Centralized information in one place. This table would be almost like a data dictionary.
How to retrieve the iformation from "GenericLookUp" table?
Just need one stored procedure to retrieve data from the "GenericLookUp" able. The SP will accept the category as parameter and the code will be something like this:
DECLARE @strCategory VARCHAR(100)
SET @strCategory = 'Customer.CreditCardType'
SELECT intID,strCode,strDescription FROM dbo.GenericLookUp (NOLOCK) WHERE strCategory = @strCategory
