0

I need to map a relation between a city council and its area of influence. Each city has one area of influence and one subarea. Each influence area has several subareas. For example,

table city
 - influence_area: Foreign key to influence_area table
 - influence_subarea: Foreign key to influence_subarea table

table influence_area:
 - name

table influence_suberea
 - name
 - influence_area: Foreign key to influence_area table

So clearly there is a redundancy in the definition. Is it good practice to reference only the subarea in the city table? What is the best practice in this case?

Ivan
  • 19,560
  • 31
  • 97
  • 141
  • 1
    Why does city have a foreign key to SubArea (ie. Each city has one special SubArea) ? – racraman Mar 09 '15 at 14:18
  • 1
    Also, can one InfluenceArea belong to more than one city ? ... Or to no cities? – racraman Mar 09 '15 at 14:19
  • Fixed the question. One influence area may have several cities. A city may have only one area and one subarea. One influence area may have several subareas. – Ivan Mar 09 '15 at 14:39
  • I added a foreign key to sub_area to show the problem with my modeling. – Ivan Mar 09 '15 at 14:40
  • Just found out that this problem is already been investigated. http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database?rq=1 – Ivan Mar 09 '15 at 15:06

1 Answers1

-1

In your model, sub area is belonging to area AND city, this is wrong.

The best way is: - sub area is belonging only to area - area is belonging only to city

in this way:

table city
 - influence_area: Foreign key to influence_area table
 - influence_subarea: Foreign key to influence_area table

table influence_area:
 - name

table influence_suberea
 - name
 - influence_area: Foreign key to influence_area table

all sub-area are belongning to city.influence_area and only 1 to city.influence_subarea

Matteo Rubini
  • 831
  • 5
  • 9
  • Thanks. Do you have a suggestion to solve the problem? – Ivan Mar 09 '15 at 14:53
  • solution is: influence_subarea: Foreign key to influence_area table and not influence_subarea – Matteo Rubini Mar 09 '15 at 15:00
  • Yeah, but in this case I have to keep two foreign tables in city and I was trying to avoid that, as if the city belongs to a subarea I already know its area. – Ivan Mar 09 '15 at 15:05
  • so, why not remove city.influence_subarea and let first sub-area belongs to city.influence_area ? city <--- area <--- sub_area – Matteo Rubini Mar 09 '15 at 15:08