Lookup Tables with fallback support

Our database has a few lookup tables that uses similar approach to implemented by .Net Framework for localized resources:

At run time, ASP.NET uses the resource file that is the best match for the setting of the CurrentUICulture property. If there is no match for the current UI culture, ASP.NET uses resource fallback. It starts by searching for resources for a specific culture. If those are not available, it searches for the resources for a neutral culture. If these are not found, ASP.NET loads the default resource file.

Let’s assume that we have a look-up table with Time Zones for different countries, states within the countries and post codes within state.
To reduce maintenance of lookup table, if most of the country has one timezone, but only a few states(or post codes) have different timezones, we are not storing information about all states/postcodes, but storing default timezone for the country and then exceptional  states/postcodes:

Country Code State Code Post Code Time Zone Comment(not a part of database table)
CC1 Null Null 1 Simple -the same Time Zone for the whole country
CC2 ST1 Null 2 In the country different states have different zones
CC2 ST2 Null 3 regardless of postcodes
CC3 ST1 1001 2 In the same state different post codes have different time zones
CC3 ST1 1002 3
CC4 null 1001 2 Country doesn’t have states, but different post codes have different time zones
CC4 null 1002 3
CC5 null null 2 Most of states in the country have one time zone, but some states have different time zones
CC5 ST55 null 3
CC6 ST5 null 2 Most of postcodes in the state have one time zone, but some postcodes have different time zones
CC6 ST5 2007 3

The algorithm to retrieve the the timezone is the following
1. Try to retrieve the row for specified country, state and postcode. If record found, we return the time zone.
2.  If record not found, try to retrieve the row for specified country and state. If record found, we return the time zone.
3.  If record not found, try to retrieve the row for specified country. If record found, we return the time zone.
4.  If record not found, throw exception- we expect that at least 1 record per country should exist.

This approach works quite well, but I have some  concerns about performance. Usually the table is stored as cached singleton with LINQ to SQL generated and loaded table.
I am not sure how good LINQ to SQL generated entities work with queries to retrieve records for specified partial keys.
I world be interesting to see some benchmarks for LINQ to SQL loaded into memory collections similar as it was done for DataTable .
I am appreciate any suggestions regarding improving performance, as well as the regarding whole fallback approach.

Advertisements