How to design DB with parent-child relationships?

Question

If I needed to represent, say, locations (e.g. countries, states/provinces/regions, cities, etc.) in a database, how would I do so in such a way that it would be easiest to query, scale the most, etc.? I plan to use this for an application that will allow users to select multiple locations and associate them to their profile.

The solution that comes to mind is this:

===========================
| Id | ParentId | Name    |
===========================
| 1  | 0        | USA     |
---------------------------
| 2  | 1        | Alabama |
---------------------------

I'm wondering if there are any potential problems with such a solution. Thanks.

Answer 1

If you know the different types of items (city state countries), you should create separate tables. Putting them all in one table will make it more difficult to query.

If you use different table types, you can enforce referential integrity, so you don't get orphans.

Think about the children!