I’m working on a database for personal use that has narrowing geographical information of various levels for the entities I’m tracking. There are five degrees: Country, Region, Subregion, Area, and Locality. Some entities only go to country, some go to region, some go all the way to locality.
I’m setting up hierarchical conditional value lists (as described in the Hierarchical Conditional Value Lists II section of philmodjunk 's Adventure 1 CLVs.
But, some countries have as many as 1400 possible localities, which means that the localities table could conceivably have as many as 10,000 records, which is a daunting number to create in order to get the db up and running (never mind maintenance…). Although, I think importing can solve this - my big concern with that is making sure the the fk’s and pk's line up (e.g., that all the fk’s for area line up with the appropriate localities…, ditto up the line with Areas to Subregions, etc)
Has anybody run into the same situation? Any thoughts or comments on streamlining things and/or data entry (all the names for each of the geographical indications are in an excel spreadsheet).