Hierarchical Database, multiple tables or column with parent id?

Question

I need to store info about county, municipality and city in Norway in a mysql database. They are related in a hierarchical manner (a city belongs to a municipality which again belongs to a county).

Is it best to store this as three different tables and reference by foreign key, or should I store them in one table and relate them with a parent_id field?

What are the pros and cons of either solution? (both structural end efficiency wise)

Answer 1

If you've really got a limit of these three levels (county, municipality, city), I think you'll be happiest with three separate tables with foreign keys reaching up one level each. This will make queries almost trivial to write.

Using a single table with a parent_id field referencing the same table allows you to represent arbitrary tree structures, but makes querying to extract the full path from node to root an iterative process best handled in your application code.

The separate table solution will be much easier to use.

Answer 2

three different tables:

• more efficient, if your application mostly accesses information about only one entity (county, municipality, city)
• owner-member-relationship is a clear and elegant model ;)