State Lookup SQL Table

lookupstate.adoc

State Name and Abbreviation Lookup Table Script

http://www.made2mentor.com/2012/03/state-name-and-abbreviation-lookup-table-script/

This is a quick script which creates a table consisting of State Names and their corresponding Abbreviations. I use this table when doing basic data quality checking and also standardizing state designations.

CREATE TABLE dbo.StateLookup
(
   StateID       INT IDENTITY (1, 1),
   StateName     VARCHAR (32),
   StateAbbrev   CHAR (2),

)

INSERT INTO StateLookup
VALUES ('Alabama', 'AL'),
       ('Alaska', 'AK'),
       ('Arizona', 'AZ'),
       ('Arkansas', 'AR'),
       ('California', 'CA'),
       ('Colorado', 'CO'),
       ('Connecticut', 'CT'),
       ('Delaware', 'DE'),
       ('District of Columbia', 'DC'),
       ('Florida', 'FL'),
       ('Georgia', 'GA'),
       ('Hawaii', 'HI'),
       ('Idaho', 'ID'),
       ('Illinois', 'IL'),
       ('Indiana', 'IN'),
       ('Iowa', 'IA'),
       ('Kansas', 'KS'),
       ('Kentucky', 'KY'),
       ('Louisiana', 'LA'),
       ('Maine', 'ME'),
       ('Maryland', 'MD'),
       ('Massachusetts', 'MA'),
       ('Michigan', 'MI'),
       ('Minnesota', 'MN'),
       ('Mississippi', 'MS'),
       ('Missouri', 'MO'),
       ('Montana', 'MT'),
       ('Nebraska', 'NE'),
       ('Nevada', 'NV'),
       ('New Hampshire', 'NH'),
       ('New Jersey', 'NJ'),
       ('New Mexico', 'NM'),
       ('New York', 'NY'),
       ('North Carolina', 'NC'),
       ('North Dakota', 'ND'),
       ('Ohio', 'OH'),
       ('Oklahoma', 'OK'),
       ('Oregon', 'OR'),
       ('Pennsylvania', 'PA'),
       ('Rhode Island', 'RI'),
       ('South Carolina', 'SC'),
       ('South Dakota', 'SD'),
       ('Tennessee', 'TN'),
       ('Texas', 'TX'),
       ('Utah', 'UT'),
       ('Vermont', 'VT'),
       ('Virginia', 'VA'),
       ('Washington', 'WA'),
       ('West Virginia', 'WV'),
       ('Wisconsin', 'WI'),
       ('Wyoming', 'WY')

Also, you can add common misspellings to the table such as ‘Pensylvania’ to further clean up your data. If your data set is international, you may want to add Canadian provinces as well. One way I use this table is with a SQL Server Integration Services (SSIS) Lookup transform and the following query.

SELECT StateName State, StateAbbrev FROM StateLookup
UNION ALL
SELECT StateAbbrev State, StateAbbrev FROM StateLookup

This standardizes everything to 2 character states abbreviations. Any records which don’t match can be handed in another branch of the data flow through fuzzy matching or another method.

sqlenum.adoc

How to Overengineer the Reference Table/Code Table

How to Overengineer the Reference Table/Code Table

http://tech.wakayos.com/?p=524

SQL Table

You probably store this in the database as a table. It could also have been column constrain, although a column constraint won’t let you run a select. None of these easly allow you to treat the reference table as an enum in C#, unless you use code generation.

Worst

Create Table  States_Ref (Id int, Description Varchar(50))

The code is numeric, so humans can’t look at raw tables to see data problems. The data type is too large. Worse would be bigint, or worst of all, just using image as a datatype in case there are a google of different states. If all reference tables use Id/Description, then select queries go from

Select customer_id, state_id, type_id, category_id … etc

to

Select customer_id, t1.description, t2.description, t3.description…

and now all queries require alias. Ugh.

Better

Create Table  States_Ref (States char(2), Description Varchar(200)).

Everyone can memorize CO, no one can memorize that 31 means Colorado. Obviously, alphanumeric codes are for the maintenance developers and uber-power users. If your users are brain damaged, the actual reference value would be hidden. In the real world, IT staff will eventually have to learn many of the numeric codes (But don’t get clever with alpha codes, don’t embed a mini-db into the codes, like CO_MTN_STATE_US.)

Best

Create Table States_Ref (
             States char(2),
             State_Name Varchar(200),
             State_Name_Short Varchar(50),
             Active bit)

The alphanumeric codes are space efficient and human memorizable. The Active bit allows certain codes to be phase out without going back to legacy data and changing the old code. The descriptive columns include the table name (In data tables, ie. not reference tables, I wouldn’t add the table name to every column, though)

C# and ASP.NET

Bad: Put the drop down list into a DataSet, reload every time.

Better Yet: Put the list into a Sorted Dictionary or Sorted List. (And do the sorting server side in SQL 1st of course), reload everyt time.

Better Yet: Batch up requests for reference tables, return them all at once in a multi query result set.

Best: Put the Sorted Dictionary into the ASP.NET Cache (Or viewstate at least). DataSets and DataTables both are too big. Don’t cache the user control– it causes peculiar behaviors

UI

Worst: Dropdowns that require a mouse to search and select.

Better: List boxes that allow viewing all rows (sometimes the list is too long and screen space to sparse, but if you can, show all the items)

Best: JavaScript instant filters for long lists

Binding

Worst: Fail to load or show a blank if the value is no longer Valid

Best Add value to list if binding and the value isn’t on the list.

Any more ideas?