Database design parent child table vs multiple tables

Question

I am trying to create a parent child relationship for country-state-city-head. I considering two approaches- 1) Create a single table-

  pk|  name             |       type       |       parent
  1 |  US               |      country     |       null
  2 |  UK               |      country     |       null
  3 | California        |      state       |       1
  4 | Los Angeles       |      city        |       3
  5 | Kent              |      state       |       2
  6 | Obama             |      president   |       1
  7 | Cameroon          |      pm          |       2

The primary key of this table would reference another table which would record population growth over period of time for state/city/country.

2) Second approach is to create multiple tables for countries, states, heads and cities and then use foreign key reference for relationship. Then primary key of each table(city/state/country) would reference the population growth table

Does approach 1 have any benefits over 2? Is it faster to query?

Answer 1

Approach 1 is an EAV table and is almost always the worst choice unless you absolutely cannot predict what fields you will need in advance (such as defining all the various things you might want to store from various medical tests). For geographical data which is not that subject to change, I would avoid Option 1 like the plague. It is harder to query, will be a source of contention for blocking and is generally just a bad idea.

Remember relational datbase work best when designed in a relational manner, don't use object-oriented thinking in defining your database tables. And if you really, really need EAV functionality, at least do it in a noSQl database that is more optimized for that sort of thing.

Answer 2

If your structure is rigid, go with the approach 2. It will let you define the referential integrities precisely, so you can never have a situation that (say) a state is the parent for the country (instead of the other way around).

On the other hand, if you anticipate dynamically adding other kinds of "nodes" (e.g. regions or municipalities under state) and other kinds of relationships (e.g. some countries may not have states at all, so cities should be directly "under" country), then the added flexibility of approach 1 might justify the "flimsiness" of the referential integrity.

If done right, both approaches should behave pretty similarly performance-wise.