Moving Data from a Grid into a Database

Question

I have the following lookup grid

x A B C D
A 0 2 1 1
B 2 0 1 1
C 1 1 0 1
D 1 1 1 0

Think of this similar to the travelling salesman with point to point, although the algorithm isn't relevant to this problem. It is More like a lookup from A->B What would be the best way to store in a database, since the time is the same both directions. A to B is 2, and B to A is 2

Start End Time
A      B     2
A      C     1
B      A     2  
etc

Doing this seems like it will be duplicating all the data which wouldn't be a good design.

Any thoughts which would be the best way implement this?

Answer 1

Don't store the duplicate rows. Just do a select like this:

select *
from LookupTable
where (Start = 'A' and End = 'B')
    or (Start = 'B' and End = 'A')

Answer 2

Agree with OrbMan. You may adopt a convention to store either the upper triangle or the lower triangle. and after loading that triangle from the database just mirror it. Doing this in the db streamer, and loader should encapsulate/localize the behavior in one place.

Oh, another thing, you should probably use a matrix implementation which is similar so that a[i,j] returns a[j,i] if i>j, 0 if i==j. You get the point... Then just have to save and load the items where i<j.