CODEWITHSUNDEEP
database-designduplicatesdistance
Nasos
Nasos

Reputation: 3

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 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

Upvotes: 0

Views: 90

Answers (3)

user2253362
user2253362

Reputation: 81

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

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

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)
);

Upvotes: 0

hrnt
hrnt

Reputation: 10142

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

Upvotes: 1

Related Questions