none
Natural or surrogate key RRS feed

  • Question

  • Greetings!  We (over)use surrogate keys and I have a chance to undo some of this. 

     

    Can a case be made for using postal code as a natural key or would that be invalidated by certain uses of postal codes. 

    For example, 55344 can be used for Minneapolis, MN or more accurately for Eden Prairie, MN.

     

    Thank you!
    Thursday, October 30, 2008 9:20 PM

Answers

  •  

    It depends.  

     

    My first recommendation would be one record per zip code, stored as a CHAR(5) datatype, natural key.

     

    This is one byte larger than using an INT, but there are only a maximum of 10000 records in this table, so no big deal.

     

    Each ZIP code has one AND ONLY ONE recommend name from the USPS.  I would use that and only that.

     

    For example, where I live the USPS will recognize:

    Los Angeles

    North Hollywood

    N Hollywood

    West Toluca Lake

    Studio City

     

    However, the PREFERED name is North Hollywood.  So, that's all we really need to store.

     

    Here are the tables you need (at least, as a starting point)

     

    tbCountry

    Country_Num smallint identity(1,1) PK

    CountryName nvarchar(255)

    USPS varchar(10)

     

    tbState

    State_Num tinyint identity(1,1) PK

    USPS CHAR(2)

    StateName varchar(255)

     

    tbCity

    City_Num int identity(1,1) PK

    CityName varchar(255)

    State_Num tinyint FK

     

    tbZip

    Zip char(5) PK

    City_Num FK

     

    Friday, October 31, 2008 12:39 AM

All replies

  •  

    It depends.  

     

    My first recommendation would be one record per zip code, stored as a CHAR(5) datatype, natural key.

     

    This is one byte larger than using an INT, but there are only a maximum of 10000 records in this table, so no big deal.

     

    Each ZIP code has one AND ONLY ONE recommend name from the USPS.  I would use that and only that.

     

    For example, where I live the USPS will recognize:

    Los Angeles

    North Hollywood

    N Hollywood

    West Toluca Lake

    Studio City

     

    However, the PREFERED name is North Hollywood.  So, that's all we really need to store.

     

    Here are the tables you need (at least, as a starting point)

     

    tbCountry

    Country_Num smallint identity(1,1) PK

    CountryName nvarchar(255)

    USPS varchar(10)

     

    tbState

    State_Num tinyint identity(1,1) PK

    USPS CHAR(2)

    StateName varchar(255)

     

    tbCity

    City_Num int identity(1,1) PK

    CityName varchar(255)

    State_Num tinyint FK

     

    tbZip

    Zip char(5) PK

    City_Num FK

     

    Friday, October 31, 2008 12:39 AM
  • Thanks for your response.  That would answer it for me, although we do deal in international addresses potentially.  I did notice in your tables you use a code for state.  Wouldn't two char abbreviation be more accurate?  Sorry to doubleback with a question.

     

    Thank you again!

     

    Friday, October 31, 2008 2:27 AM