Database Schema design

Question

I am trying to design a location lookup in which the user can specify a location to any desired level of accuracy. eg. one of Country, State, City, Borough etc,

I have a used a common location table, which will then be used in a lookup with the table name selected dynamically, but was wondering if there is a feasible alternative way to do this.

alt text http://img386.imageshack.us/img386/3842/locationschemadh6.png

Edit The hierarchical table looks like the way to go. Thanks for the tip.

Answer 1

You might consider something like:

locations
  id int
  parentId int
  name varchar(45)

From this perspective you could load any type of location with any level depth.