February 13, 2005 at 6:01 am
I have a database that contains many, many types (or lookup) tables. Almost 100% of these tables have the structure:
TypeValue int; TypeDescription varchar(n)
I have often thought that it would be convenient to put all of these values in one Master Lookup Table with a third column which would hold the "Lookup Type". This way there are far fewer tables in the system and far less stored procedures. Instead of needing a stored proc for each table I could call one common proc and simply pass it the "Lookup Type" as a parameter.
I'd appreciate any thoughts or feedback on this approach. Can anyone point out why this is a bad idea?
Thankx,
Rusty
February 13, 2005 at 4:16 pm
See the following article and heated discussion.
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
My personal view on it is that if your Master look up table does not have a vast number of rows in it then it is a valid approach.
If you end up with a great number of rows then you will be doing many joins where you vast master lookup table is connecting to a huge table and this might hurt performance.
I have tried segmenting the one huge table by using views to extract the individual types lookup and this worked well.
I had four stored procedures to maintain the master look up table and a view for each lookup table.
February 14, 2005 at 1:07 am
I have used both approaches and they both have there positives and negatives. The single table approach is great in keeping your databases from being populated with many code description tables, especially if you have a need for supporting multiple languages for the descriptions. The biggest down side is that you cannot define referential integrity unless you add an extra column to the child table to discriminate which of the code value sets you are referring to. This isn't an issue if you have already made a decision that, for performance reasons, you will not be enforcing RI to the code descriptions. Unfortunately, I have also seen to many situations where the programmers are not careful enough, and relying on the application code to enfore the RI is a quite risky if you are dealing with critical enterprise data. These days I tend to opt for the many table approach so I can enforce the RI and ensure that a programming error isn't going to corrupt the data.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
February 14, 2005 at 2:21 am
Let me add this one: http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 29, 2005 at 1:05 pm
Here is a Master reference lookup table. it WAS a good idea until the user community wanted to dynamically add records. security became a nightmare. then users wanted lookup within look up tables. then they wanted the admin abilities to add, re-sort, and delete records. imagine the nightmare holly geez.
the best solutions and most dynamic is SINGLE lookup tables. they are simple and easy to maintain. plus they are exceptionally dynamic.
good luck re-inventing the wheel.
CREATE TABLE [dbo].[Master_Reference] (
[Master_Reference_ID] [int] NOT NULL ,
[Master_Reference_Category_ID] [int] NOT NULL ,
[Master_Reference_ID_SearchBack] [int] NULL ,
[Master_Reference_Sort_Seq_Nbr] [int] NULL ,
[Master_Reference_Desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_Updated_LoginIDName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Updated_Timestamp] [smalldatetime] NULL ,
[Master_Reference_Desc2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [smalldatetime] NULL ,
[LoginIDName_Createby] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Master_Reference_Category] (
[Master_Reference_Category_ID] [int] NOT NULL ,
[Master_Reference_Catergory_Desc] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_Updated_LoginIDName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Updated_Timestamp] [binary] (8) NULL
) ON [PRIMARY]
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply