Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    How to design a table with adjacent cities?

    I'd appreciate some help to my little problem:

    There are pairs of items that are connected, like adjacent cities. A rough example (maybe geographically unprecise):

    table Adjacent
    city1 | city2
    -----------------
    NYC | Jersey City
    Jersey City | Hoboken
    Chicago | Oak Park

    Is this the best way to design that table? What's the best way to get all adjacent cities for a city?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Have a table for cities
    eg:-1
    Code:
    ID  City
    666  New York
    35  Jersey City
    999 Hoboken
    have a table for cities that are adjacent to other cities with a PK on each to the ID in cities
    Code:
    IDA IDB
    666 35
    35   999
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I assume that if City A is adjacent to City B, then City B is also necessarily adjacent to City A?
    If so, you'll need to tweak healdem's example a bit to maintain relational integrity.
    To prevent duplicates, enforce a constraint that City A's primary key is always lower than City B's primary key. To get a full set of relationships from the table, use a view with a UNION query:

    Code:
    select	CityA as City,
    	CityB as AdjacentCity
    from	CityPairs
    UNION ALL
    select	CityB as City,
    	CityA as AdjacentCity
    from	CityPairs
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2013
    Posts
    2
    Quote Originally Posted by blindman View Post
    I assume that if City A is adjacent to City B, then City B is also necessarily adjacent to City A?
    Yes, I forgot to mention that.

    I was thinking about using UNION but wasn't sure if there would be another solution.

    Thank you, guys!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    There are several solutions.
    I just gave the one I like best.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •