Sql Server - Setup 30 Lookup Tables 1

[BxWill]

Timely insight needed.

Currently using TSQL to validate data by initially bulk inserting from a text file (containing approximately 30 lookup fields) to a temporary staging table (Staging table 1). Then, inserting into staging table 2 and validating for duplicate records and then finally inserting from staging table 2 into the final destination table.

It appears that to include a validation on a lookup field using a CASE statement to determine if the value is valid or not is not efficient if the number of lookup values is rather exhaustive - say, for all 50 states in the United States.

It appears that the preferred option is to use foreign keys insead of a check constraint and create the numerous look up tables. Creating the lookup tables with a auto id field and specifying the primary key as the id field. But, then specifying a UNIQUE constraint on the field "Code" within the CREATE TABLE section when I setup each lookup table.

[Code] [varchar] (15) NOT NULL UNIQUE,

Considering that I have approximately 30 lookup fields that is within the text file that I have to load every month, is there another method to use that does not involve the setup of foreign keys?

It appears that I have to add 30 foreign key constraints - one for each lookup table and also add multiple inner joins when building queries. Is this correct?

Further, it appears that one has to know the id of the associated value within the lookup table. For example, within the WHERE section of option 2 query below, I have;

where EL.EquipCodeCode_Id in (6,10)
and SL.StateCode_Id = 1

Also, I am currently reviewing the implementation of clustered primary keys, indexes, etc. but not familiar with any advantages that these will provide or how to setup.

Any insight regarding the setup of approximately 30 lookup tables is appreciated.


The overall objective is to initially setup the database as efficiently as possible to ensure that the setup of extensive queries/reports involving the lookup fields will be seamless.

Use CapitalEquipment



IF OBJECT_ID('tempdb..#Bureau1') IS NOT NULL
BEGIN
	DROP TABLE #Bureau1
END
GO
--select * from #Bureau1
CREATE TABLE [#Bureau1] (		
		[Equipment_Code]   		[varchar] (60)  NULL,
		[Account_Number]   		[varchar] (60)  NULL,
		[Sequence_Number]   		[varchar] (60)  NULL,
		[Equipment_Condition_Code]   	[varchar] (60)  NULL,
		[State_Code]   			[varchar] (60)  NULL,
		[Tracking_Number]   		[varchar] (60)  NULL,
		[Sale_Date] 			[varchar] (60) 	NULL,
		[Sale_Amount]  			[varchar] (60) 	NULL,
		[Purchase_Zipcode]   		[varchar] (70)  NULL
)	
GO


BULK INSERT #Bureau1
   	FROM 'C:\Data\Equip_2016.txt'
	
   WITH
      (
         FIELDTERMINATOR = '\,',
		 ROWTERMINATOR = '\n',
		 FIRSTROW = 2
      );
GO



IF OBJECT_ID('CapitalEquipment..Equipment_2016') IS NOT NULL
BEGIN
	DROP TABLE Equipment_2016
END
GO

CREATE TABLE [Equipment_2016](		
		[Equipment_Code]   		[varchar] (5)  NOT NULL,
		[Account_Number]   		[varchar] (45)  NOT NULL,
		[Sequence_Number] 		int NOT NULL,                   
		[Equipment_Condition_Code]   	[varchar] (1)  NULL,
		[State_Code]   			[varchar] (6)  NULL,
		[Tracking_Number]   		[varchar] (15)  NULL,
		[Sale_Date] 			date  NULL,                      
		[Sale_Amount]   		int  NULL,
		[Purchase_Zipcode] 		[varchar] (9)  NULL,
		Constraint PK_Equipment_2016 Primary Key ([Equipment_Code], [Account_Number], [Sequence_Number])
	
		Constraint FK_Equipment_2016 FOREIGN KEY ([State_Code]) REFERENCES Lookup_State_Code ([Code]),
		Constraint FK_Equipment_2016 FOREIGN KEY ([Equipment_Condition_Code]) REFERENCES Lookup_Equipment_Condition_Code ([Code]),
		.
		.
		.
		WITH (IGNORE_DUP_KEY = ON)


)
GO

/****************************
CREATE LOOKUP TABLES
*****************************/
 
IF OBJECT_ID('CapitalEquipment..Lookup_State_Code') IS NOT NULL
BEGIN
	DROP TABLE Lookup_State_Code
END
GO

CREATE TABLE [Lookup_State_Code](		
		StateCode_Id int identity not null primary key (StateCode_Id), 
		[Code] [varchar] (15) NOT NULL UNIQUE,
		[Desc] [varchar] (100) NULL
		
	)	




IF OBJECT_ID('CapitalEquipment..Lookup_Equipment_Condition_Code') IS NOT NULL
BEGIN
	DROP TABLE Lookup_Equipment_Condition_Code
END
GO

CREATE TABLE [Lookup_Equipment_Condition_Code](		
		EquipCodeCode_Id int identity not null primary key (EquipCodeCode_Id), 
		[Code] [varchar] (15) NOT NULL UNIQUE,
		[Desc] [varchar] (100) NULL
		
	)	


/****************************
DATA WITHIN LOOKUP TABLES
*****************************/


Equipment Condition Lookup Table

Value	State		
000	NONE
001	Not Known
CCC	Not Known
AVG	AVERAGE
DMG	DAMAGED
EXC	EXCELLENT
FAI	FAIR
GOO	GOOD
POO	POOR
VGO	VERY GOOD



State Code Lookup Table

Value	State		
AL	Alaska
AZ	Arizona
.
.
.
CA	California
.
.
.

/******************************************************************
QUERY OBJECTIVE

Extract equipment that are in excellent and very good condition that are located in Alaska
*******************************************************************/


Option 1 - Without Lookup Tables

Select Account_Number, Equipment_Condition_Code, State_Code
from Equipment_2016
where Equipment_Condition_Code in ('EXC','VGO')
and State_Code = 'CA'

Option 2 - With Lookup Tables

Select Account_Number, Equipment_Condition_Code, State_Code
from Equipment_2016 EQ
INNER JOIN STATE_LOOKUP SL
ON EQ.State_Code = SL.Code
INNER JOIN EQUIPMENT_CODE_LOOKUP EL
ON EL.CODE = EQ.Equipment_Condition_Code
where EL.EquipCodeCode_Id in (6,10)
and SL.StateCode_Id = 1

 

[gmmastros]

You don't need to use an auto id field for the primary key. You can use your code field just as well. What I mean is, let your code field be the primary key. Since it's the primary key, it will still need to be unique.

ID fields are ideal for primary keys because the primary key is usually created as a clustered index, and the physical sorting of the table is based on the clustered index, and it's faster to sort integers than it is to sort varchars. However, I suspect that each lookup table is probably not all that large, so performance wise, you probably won't notice. Anyway... give it a shot to see how it works for you.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom