US states and adjacent states

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  January 4, 2006 at 7:23 am

  #91156

  I need to store US states and the states adjacent to them in a SQL 2000 database.

  How does the following sound?

  Table 1

  State abbreviation     primary key

  Other fields holding information about the state ....

   

  Table 2

  State abbreviation     primary key

  Adjacent 1 abbreviation

  Adjacent 2 abbreviation

  etc ...

  Is there a better way to model this?

   

  Thanks

  jmatt

• Out For Justice

  SSCrazy

  Points: 2733

  More actions

  January 4, 2006 at 8:10 am

  #612622

  Obviously it depends on how you intend to use the data in order to define the best model, but I would make Table 2 a cross reference table with 2 columns,

  State Abbreviation

  Adjacent Abbreviation

  and have a primary key on both fields.

  SELECT [Adjacent Abbreviation] FROM [Table 2] WHERE [State Abbreviation] = '<my state>' 

  will give you all the adjacent states for <my state>

   

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  January 4, 2006 at 8:29 am

  #612635

  Another thought that will save you some work...  Or maybe make your life harder. 

  This sort of table is typically used for path traversal algorithms and you will eventually need to see if California is next to Nevada, and if Nevada is next to California.

  You only need to enter each pair into Table2 once.  Simply add an "or" in the where clause, or write a simple UDF to see <my state> appears in either StateAbbreviation or in AdjacentAbbreviation.

  Wayne