CODEWITHSUNDEEP
sqldatabase-design
Jigar Parmar
Jigar Parmar

Reputation: 31

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 which satisfy these requirements?

Thanks in advance.

Upvotes: 3

Views: 518

Answers (5)

RC_Cleland
RC_Cleland

Reputation: 2294

For SQL Server 2008 the choice is the hierarchy data type

Here is a link about the performance

Upvotes: 1

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Reflexive relationship.

enter image description here

Or, if you prefer nested sets.

enter image description here

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 1

Brian Webster
Brian Webster

Reputation: 30855

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.

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

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

Upvotes: 1

Related Questions