Database design
-
A colleague and I are having discussion on how to handle lookup tables. These are tables that can be conceptualized as combo box drop down list. In general they are small collections of like things. I would like to place them all in one generic table and use views to segment the table into its various component pieces. She is opposed to this and thinks it would be better to many small tables for each of the lookup items. We are looking for opinions of performance concerns, supportability concerns - which would you choose?
This application is a marketing database, which at its heart creates a mailing list and manages communication to that list.
An example is a list of titles (like Mr. or Ms.), suffixes (like Jr. III), genders and Mailing events. The generic table would have a number of possible values and look something like this
CREATE TABLE [dbo].[T_Lookup_LKP] (
[LKP_ID] [int] IDENTITY (1, 1) NOT NULL ,
[LKP_Type] [int] NOT NULL ,
[LKP_Code_1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LKP_Code_2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LKP_Number_1] [int] NULL ,
[LKP_Number_2] [int] NULL ,
[LKP_Link_1] [int] NULL ,
[LKP_Link_2] [int] NULL ,
[LKP_Date_1] [datetime] NULL ,
[LKP_Date_2] [datetime] NULL ,
[LKP_Desc_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LKP_Desc_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LKP_CreateDate] [datetime] NULL ,
[LKP_CreateBy] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LKP_ModifiedDate] [datetime] NULL ,
[LKP_ModifiedBy] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
and a possible view would be
SELECT LKP_ID AS Mailing_Id, LKP_Type, LKP_Code_1 AS Mailing_Code, LKP_Number_1 AS Mailing_Number, LKP_Number_2 AS Mailing_Weeks_from_Last,
LKP_Link_1 AS Mialing_Next, LKP_Desc_1 AS Mailing_Desc
FROM dbo.T_Lookup_LKP
WHERE (LKP_Type = [dbo].fn_SANK_Lookup_Number('Mailings', 'Record Type'))
The function looks up the corresponding number for the type of record being searched.
-
You owe her diner
. -
And this one (check out the discussion after the article... he pretty much gets destroyed all across the board).
-
I am with RGR'us on this! You will end up jumping through hoops not only to populate a single table, but retrieving the data will become a nightmare - not to mention the fact that when you bring on someone new, they will have viscious learning curve to understand how to use these tables.
Just name them VERY well, and use multiple tables...
I say, RuthCriss Steakhouse...
I wasn't born stupid - I had to study.
-
From the "lookup table madness" article, the overiding factor for me was the data integrity issue.
eg. You have CustomerType and ProductType ... these get merged into a single "Types" table! What you've now just allowed at the schema level is that a "Customer" could get assigned to being a ProductType ... and there's pretty much nothing you can do to prevent this from happening (alot more Hoops!?).
As such, your data integrity goes out the window ... its now in the DB development standards i implemented at my company, and "The Arguers" didn't have much of a leg to stand on after this!?
-
Would've loved to see their face after you gave them that argument.
-
Been there done that, had some folks who came up with a wonderful code lookup table that encompassed every possible code known to man from state abbreviations down to job titles and genders. Not only was the thing a bear to work with ("damn what's the code type again? We need a lookup table for the code types...
"). It was so complex/difficult to work with that they ended up with repeat data in the table ("I couldn't find that code in the table so I added a new codetype...") and often spent a lot of time trying to relate code types to each other (e.g. "states have counties, counties contain cities, how do I join them all together again?"). When in doubt, KISS always pays off in the end.Joe
-
Data integrity is my main reason for individual tables. We have a database that started with one large table and are gradually moving off of it. I've had many production problems resulting from the use of that single table and the misunderstanding of how to use it, it's complexity, etc. The other issue that I have is that because of the generic nature of the structure, people tend to want to put all sorts of things in the table--even things that are not codes for the application! That's a whole separate set of problems. In our database world today, there's no reason a database can't handle more tables (which is another argument for a single table).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply