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

This seems fairly suitable to scalability, I might however include an additional column to identify the actual row/entry (type of entity if you will).

This will allow you to easily query based on selection (country, city, etc.). You might find though that the hiracrchy can cause you some pain, seeing as the entire structure will be in a single table, and you do not know the depth to start with.

In the end, the design will depend on the known number of sub sections you might have, and wether such normalization might in fact make life more diffucult, rather than not.

Answer 2

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!

Answer 3

I use something similar and have had much success with it. A recent Question of mine might come in handy.

In some cases (such as this) I dont want to be tied to a table structure. Self referencing can be a great thing. I dont understand why it's so acceptable to have tree-like structures using the same object type in code, but its shunned by programmers when it comes to databases.

Answer 4

Depending on your audience, you may also want to consider separating out the name values by type so they can be localized without duplicating the hierarchical structure.