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|
|CC4||null||1001||2||Country doesn’t have states, but different post codes have different time zones|
|CC5||null||null||2||Most of states in the country have one time zone, but some states have different time zones|
|CC6||ST5||null||2||Most of postcodes in the state have one time zone, but some postcodes have different time zones|
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.