CODEWITHSUNDEEP
sql
Dan
Dan

Reputation: 69

Structure for 3 or more tables with relationship

I need help about database structure,

so I need to join 3 tables but I'm confuse what structure is the right way.

Region_table

id     region_name
1      Region1
2      Region2

Province

id    province_name
1     Province1

City

id     city_name
1      city1

I'm thinking to create new table that connects all.

like id, region_id, province_id, city_id

region_province_city

id    region_id    province_id    city_id
1     1            1              1 
2     1            1              2

or I can do like

Province

id    province_name  region_id
1     Province1      1

City

id     city_name     province_id
1      city1         1

Upvotes: 0

Views: 275

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I would generally suggest the hierarchical structure. That is, adding the parent id to each of the two tables.

Why? This guarantees that the region on a given province is always the same. That can be hard to guarantee in the 3-way junction table. After all, you don't have an independent relationship among the three entities. You have a hierarchical relationship and you would like to enforce that data integrity.

An example of an independent relationship would be:

  • Customers
  • Items
  • Payment Method

Presumably a customer could purchase any item using any payment method -- and even use different payment methods on the same item.

Would I always recommend this structure? Well, no. If the data is not being modified, then you are pretty safe with the three-way table. It works very well for looking up information about a "city". It can be verified when created. And it might provide some simplification (particularly if the hierarchies are deeper).

That said, it works best when your joins are to the lowest level. It is a little tricky to get the region for a province. A typical solution would be to augment the data with NULL values for city to get that information.

Upvotes: 1

Related Questions