How design SQL tables for these requirements

I have data as CITY and AREA.

1. Each CITY has multiple AREAS
2. Each AREA has multiple AREAS (here there is no end, dynamically user can add AREAS under the child AREA like AREA->AREA->Area->AREA....->AREA)

So, how to design such table structure 开发者_StackOverflow社区which satisfy these requirements?

Thanks in advance.

City Table

• CityID (PK)
• CityName

City Areas Table

• CityID (Composite (two-column) PKey)
• AreaID (Add a Unique Index to this column, if you want an area to be ownable by only one city)

Areas Table

• AreaID (PK)
• AreaName

Area Area Mapping Table

• AreaID (Owner Area) (Composite (two-column) PKey)
• AreaID

Rules

• In order to map one area to another, there must be a record for each area in the Areas table.
• In the Area Area Mapping Table, you must determine if these relationships are two-way or one-way. In my opinion, it will be one way. The first AreaID is the area that owns the second AreaID.

table AREA:

• id (PK)
• parent_area_id - the area to which it belongs - can be NULL if no parent area (FK on AREA table)
• city_id - the city to which it belongs - you can enforce from your business logic that a city_id to be NULL if parent_area_id is completed, or vice versa (FK on city table)
• other useful columns

table CITY:

• id (PK)
• other useful info

You might be interested in reading about Managing Hierarchical Data in MySQL - it also applies to other DB engines

That would be a tree (or possibly a hierarchy). The most common solution, which you will see in other answers here, is to use the adjacency list model. However, the other 'big' idea to consider is the nested sets model. A useful book on the subject is Joe Celko's Trees and hierarchies in SQL.

Reflexive relationship.

Or, if you prefer nested sets.

For SQL Server 2008 the choice is the hierarchy data type

Here is a link about the performance