SQL Server: What would be the appropriate way to define the relationships (borders) between states?

Question

Suppose you have to build a DB tables that depicts states and their borders between each other. Let's say the States table would look like:

States: Name(PK), Size, etc...

What would be the appropriate way to define the relationships (borders) between states?

I came up with three alternatives -

1. Defining a Borders table with primary key combined by two fields: Id(PK), StateName(PK,FK)
2. Defining a Borders table with StateName1(PK,FK), StateName2(PK,FK)
3. Defining a Borders table with a concatenated value of two states' names.

Some more information:

• I am going to query the data as follows: someState.HasBorderWith(State anotherState)
• I use EF 4.0 with POCO entities.

Answer 1

Option 2 is the standard implementation of such a relationship. I don't understand what you mean w/ option 1, and option 3 isn't an option - it would be a nightmare to query this!

Answer 2

Personally, I would create a table with:

 Id (PK)
 StateName1 (FK)
 StateName2 (FK)

This is basically your second option with an additional Id field (which isn't necessary, but use Id fields on almost every single table out of practice).