I'm not sure if this best belongs here, but I'm using MySQL. I want to make a 'list' of geographical names. This is for metadata describing pictures and articles. I did some power-googling, had trouble finding stuff. My idea for the list:

-one table with all the names in it. (countries, prov's/states, districts/counties, municipalities, etc.)
-each name type is assigned a level # according to its type, 1 for countries, 2 for prov/states, down the line to the smallest unit (could be street names/numbers or even individual addresses). geographical features/areas unclassifiable by political boundaries are assigned #0.
-the database starts off with some practical names in it - the ones most likely to be needed by users. the user selects from level 1 and down to where they feel is specific enough. (if they're unsure about the exact location, they can stop at the province/state level, or skip the district/county level)
-a level 0 name can be added anywhere in the tree
-if a name is not in the list, which may often happen with city names or lesser levels, there is a box to type one in. This 'proposal' can then be approved by webmaster or whomever, and added to the database.
-there is no actual heirarchy, it's implied by sorting items by level #'s
-only one of each type may be selected for each entry, stored in another table, types comma-delimited, disparate locations semicolon-delimited.

Is this practical and efficient? Any links about this type of database? Thoughts? Opinions? Fire at will!

This is not efficient , think abt it, if you want to list addresses from 3 countries, imagine the number of recursive joins to the same table to accomplish this?

A better way is to design this relationally
Create table countries, PK COUNTRY_ID
Create Table prov's/states , PK STATE_ID, FK COUNTRY_ID
Create table districts/counties, PK DISTRICT_ID, FK STATE_ID
Create table municipalities, PK MUNCIPALITY_ID, FK DISTRICT_ID

PK - Primary Key cannot be null
FK - Foreign Key can be null
BTREE index on all keys.

The hierarchy that you want can be clearly obtained by this.
Let me know if this design is plausible then we can discuss solutions to your query requirements .

Edit: I don't know if you read the original post, but I'm catching up on foreign keys and BTREE. I'm new to this. Would this be using MyISAM or InnoDB?