Database Distances Cities Table

I need a DB model, that can handle an exclusive PK with 2 values.

Lets say we have 3 cities and they are all开发者_JAVA技巧 connected.

The distances are:

(1)->(2) = (2)->(1) = 5

(1)->(3) = (3)->(1) = 3

(2)->(3) = (3)->(2) = 4

The complex point is that i dont want duplicate entries!

Thx in advance

---

You need three columns. SourceID, DestID and Distance. Store in SourceID the smaller ID number and create a unique constraint on (SourceID, DestID);

---

Enforce the order to prevent duplicates. The same tactic works whether you're storing city names or city id numbers.

CREATE TABLE distances (
    start_city VARCHAR(35) NOT NULL CHECK (start_city < end_city),
    end_city   VARCHAR(35) NOT NULL,
    km_distant INTEGER     NOT NULL CHECK (km_distant > 0),
    PRIMARY KEY (start_city, end_city)
);

---

You can use latitude and longitude for calculate distances. See here