Binary Relationship Tables? 1

[Fhwqhgads]

I am trying to "hard-code" a binary relationship into a table form. For example, imagine a table showing distances among pairs of American cities. Each city would get a row and a column. Then the number in row X column Y would be the distance between city X and city Y.
This table would only be for lookup, after initial population I do not plan on altering it [much].

Is there a simpler way of doing this than just creating a table and growing it column by column?

Thanks!

 

[gmmastros]

Yeah... Don't do that. The maintenance on such a table would be too much to bear.

Instead, get the latitude and Longitude between each city and calculate the distance. This thread183-1197121 demonstrates how you can calculate the distance between 2 cities (assuming you know their latitude and longitude).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Fhwqhgads]

I think there is a misunderstanding. I am not actually trying to find the distance between two citices, I was using that as an example. The actual thing I am trying to find is far more complicated and takes a good chunk of computation time to do...so I was hoping to just compute these values once and store them for future lookup.

 

[gmmastros]

My apologies for misunderstanding the question.

If I understand correctly, you have 2 items with a calculated value that you want to store. Calculating the values in real-time is either impossible or time-prohibitive.

In this case, I would create a table that has 3 columns.

ID1, ID2, CalculatedValue

Even if this table becomes large, the performance should still be very good because you won't be storing a lot of data for each match.

There are several problems with storing the data as you previously mentioned. SQL Server has a limited number of columns that you can have in a table. While this number may be large enough to accomodate your data, eventually you may run in to this limitation, causing you to re-design it. Also, selecting the data from this table may be a bit difficult to, because you won't (necessarily) know what the column name is. This would require you to use dynamic SQL, which has adverse security implications. And last, but not least, it will be difficult to create indexes on this table, so with a large amount of data, performance would suffer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Fhwqhgads]

Thanks! That is the sort of information I needed. The solution you suggest takes 3 times as much physical room, but seems much more inline with how SQL should be used.