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?

Some cities straddle a border. In the US, for example, "Texarkana, Texas" is half a city; "Texarkana, Arkansas" is the other half. For population purposes, it's a single city (actually, a single "metropolitan statistical area"). For other purposes, it might be two cities. I've seen addresses that were "in" one county geographically, "in" a second county for emergency services, and "in" yet a third county for nonemergency services. — Mike Sherrill 'Cat Recall', May 02 '12 at 10:21

HLGEM · Accepted Answer · 2012-07-10T20:18:45.810

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.

score 1 · Answer 2 · answered May 02 '12 at 16:53

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.

score 0 · Answer 3 · answered May 02 '12 at 08:15

I think it depends what else (if anything) you want to store in the database. For example, if you want to store country-specific data, such as 'capital city' and etc for the other entities then I would go with approach 2. However, if all you need to represent is 'population groups' then 1 seems fine and is simpler. Your parent id field should be a foreign key though ie to the pk of the parent on the same table. I would consider making type a foreign key to a Type table so you can store it as a number rather than a string in your main table. As long as your keys are indexed I doubt you'll see much difference in performance.

I dont want have any country/city specific data. i dont have too many types so i believe a string should be ok. — blue01, May 02 '12 at 11:48

Database design parent child table vs multiple tables

3 Answers3