Storing information for drop-down menus

Question

Suppose you're building an HTML form and you want to have 2 or more drop-down menus, where after picking an option from the first, the 2nd menu is populated, and so forth. For example, choosing a country would bring up a list of provinces for that country, which would in turn list the cities.

How would you store that sort of information in a database? Would you have a table Countries(id, name), Provinces(id, country_id, name), Cities(id, province_id, name) or would you have just one big denormalized table Entries(country, province, city)? (Why?)

Answer 1

How about a flat table for each level of items, with an attribute that tells you which higher level category each item belongs to.

then your queries might go:

SELECT name FROM countries
SELECT name FROM states WHERE country="selection"
SELECT name FROM cities WHERE state="selection"

Answer 2

I'd use the normalized form because (as is usual with normalized forms) updates would be easier and safer.

Imagine that "Canada" changes its name to "America's Hat." With the normalized form, you update one row, and its dependent provinces and cities are still accurate. With the denormalized form, your update would have to update a row in the table for every city in Canada, which would be less efficient (and, [sadly] in some RDBMSes, not atomic).

Additionally, consider that you might add more columns to these tables in the future, like country codes for countries, province abbreviations, city postal codes, etc. All of this data would need to be duplicated for every row in the denormalized table; in the normalized tables, you (again) would have the data in the one appropriate and canonical place for it.

As an aside, you might consider losing the "id" columns (presumably auto-generated integer keys?) and using natural primary keys: perhaps country codes for the countries, "name" for the provinces, and longitude/latitude columns for the cities (assuming that "name" wouldn't serve the necessary distinction).