I'm designing a system which needs to report basic distance between zip codes. It will use the Google Distance Matrix API but I want to cache the results in the database so that multiple requests for the same data points don't result in duplicate API calls.
Distance class might look like this:
A basic data structure for the cache might look like this:
Since I'm not using fine-grained detail concerned with changes due to one-way streets or divided highways, interchanges, etc., it therefore doesn't matter to me whether I'm getting the data from point A to point B or from point B to point A. But I'm not sure how to express this with full normalization in the database. With this primary key, it would be perfectly legal for the same distance to exist in two separate row, i.e.
var row1 = new Distance("00001", "00002");
var row2 = new Distance("00002", "00001");
It would probably be a good idea for me to require a
SortedList parameter in the constructor, but is there a way I can design it from the database side to enforce full normalization?