CODEWITHSUNDEEP
postgresqldatabase-designmultiple-tablesself-reference
blue01
blue01

Reputation: 2095

Database design parent child table vs multiple tables

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?

Upvotes: 4

Views: 3610

Answers (3)

HLGEM
HLGEM

Reputation: 96580

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.

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

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.

Upvotes: 1

Martin Wilson
Martin Wilson

Reputation: 3386

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.

Upvotes: 0

Related Questions