Take the 2-minute tour ×

I'm trying to design a database table that lists "prices" for distances, "City A" to "City B". City B to City A should be the same price, so it would be redundant to store this information twice.

How should I design the table such that given 2 cities, I can look up the price without having to store it twice (as A,B,price and B,A,price)?

My idea is that I can store it "alphabetically" such that the "earlier" city would always be in the left column, and the later city would appear in the right column. Then when querying the DB, I just have to do the same thing and swap the order if necessary.

share|improve this question
City B to city A isn't necessarily the same price. There are lots of reasons why not, on both the demand side and the supply side. –  Walter Mitty Dec 30 '10 at 1:58
@Walter: How can you say that without knowing anything about my application? Do you even know what the "price" is for? –  Mark Dec 31 '10 at 2:27
add comment

4 Answers

Well you can do that with an OR clause in your select (WHERE (A = 'city A' and B= 'city B') Or (A = 'city B' and B= 'city A'), but honestly storing it twice will probaly mean faster querying.

share|improve this answer
Using the "or" solution also wouldn't prevent it from being added to the DB twice (even worse, with different prices!) which could cause some major inconsistencies. Same problem the latter solution, actually. –  Mark Dec 29 '10 at 22:41
add comment

Probably the best fast&generic solution is to live with a constraint, for example CityId1<CityId2 in all rows, and use some kind of OR or double select when retrieving the data from the table.

If it's more about "database design", then just feed it into your favorite ER modeling tool, and observe the result.

share|improve this answer
Similar to my question amendment. Seems like a reasonable solution. Do databases (such as PostgreSQL) allow such constraints, or would I enforce it only in code? –  Mark Dec 31 '10 at 2:18
Constrains like this can be added, but I don't see a reason to complicate the DB schema here. See here postgresql.org/docs/8.1/static/ddl-constraints.html –  Mirek Kratochvil Dec 31 '10 at 16:13
add comment

Store the city pairs only once in the table. Store the data in alpha order with the first alpha city in the first column using a stored procedure to sort the data prior to insertion. Create a unique index on the two city columns. Create a retrieval stored procedure which will sort the supplied cities first then query the table. Here is some quick work using SQL Server 2K8 Express.

CREATE TABLE [dbo].[Distance](
    [D_Id] [int] IDENTITY(1,1) NOT NULL,
    [D_City1] [nchar](10) NOT NULL,
    [D_City2] [nchar](10) NOT NULL,
    [D_Distance] [int] NOT NULL


Insert Distance

select * from Distance where D_City1 = 'a' and D_City2 = 'b'
Drop procedure Get_Distance ;
Create procedure Get_Distance 
@1City nvarchar(10)
, @2City nvarchar(10)

Declare @1AlphaCity nvarchar(10), @2AlphaCity nvarchar(10)
Select @1City, @2City, @1AlphaCity, @2AlphaCity
set @1AlphaCity = @1City
Set @2AlphaCity = @2City
If @1AlphaCity > @2AlphaCity 
    Set @1AlphaCity = @2City
    Set @2AlphaCity = @1City
Select @1City, @2City, @1AlphaCity, @2AlphaCity


EXEC dbo.Get_Distance 'C', 'B'
share|improve this answer
Cool, didn't know you could do all that in SQL :-D –  Mark Dec 31 '10 at 2:24
add comment

You are speaking of the concept of the compound key, where both value_1 and value_2 determine which record is pulled.

I would say simply design it where your fields would city_1, city_2, price. Then programmatically handle the logic to define the proper query.

share|improve this answer
This is too vague... how would I "programmatically handle the logic"? That's precisely what I'm asking about. It's not simply a compound a key, it's a compound key where the order doesn't matter. –  Mark Dec 29 '10 at 22:40
If you are asking about how to "programmatically handle the logic" then maybe this shouldn't be tagged solely as "database-design". –  jondavidjohn Dec 29 '10 at 22:51
Well... naturally when designing the database you have to consider querying the database... perhaps I didn't tag it with all the tags I could/should have, but the question should still speak for itself :) –  Mark Dec 31 '10 at 2:20
add comment

Your Answer


