Two-key table in database?

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 appe开发者_运维问答ar in the right column. Then when querying the DB, I just have to do the same thing and swap the order if necessary.

---

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.

---

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.

---

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
) ON [PRIMARY]

GO


Insert Distance
Values
('a','b',30)
,('b','c',40)
,('c','z',40)
,('d','z',40)
,('e','z',40)

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

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

GO

EXEC dbo.Get_Distance 'C', 'B'

---

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.