Looking for some clever sql strategies for dealing with a lookup table

Question

I want to store the distance between different locations in a table.

CREATE TABLE `example` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `from` VARCHAR( 8 ) NOT NULL ,
    `to` VARCHAR( 8 ) NOT NULL ,
    `distance` DECIMAL( 6, 2 ) NOT NULL ,
    PRIMARY KEY ( `id` ) ,
    INDEX ( `from` , `to` )
 )

As a distance between two points is measured, it is inserted into the table. Getting the distance between 'from' and 'to' is obviously very easy. However the distance between 'to' and 'from' is exactly the same. I dont want to make another row just to switch the 'to' and 'from'. I would also prefer not to have to make a lookup table for this table, to accomplish this.

The sql to select the distance will be called quite a lot so it needs to be an efficient query.

I have simplified the table so don't worry about the places being varchars etc.

Anyone have any strategies they could recommend?

Answer 1

Call the locations 'A' and 'B'. If A < B, then search for A in 'from' and B in 'to', otherwise, search for B in 'from' and A in 'to'. Same logic when inserting values.

That way you only store each combination once, and it is fast to query.

Answer 2

May I suggest something entirely different. Each point has coordinates relative to the same point, so the distances will be calculated when giving two points, and not saved in the DB.

Now, if those are points on maps/Graph, then another solution is in place.