0

Does anybody have experience or know how to correctly design database relationships for where

Provinces ->hasmany cities City ->hasOne Province

the problem is different cities (based on geography / province location) may have the same name. Thanks for the help.

Improve this question
1
  • Warning: Texarkana is in both Texas and Arkansas
    – Neil McGuigan
    Commented Jun 2, 2016 at 22:18

1 Answer 1

Reset to default
1

How about this:

province(province_id, province_name, ...)
cities(city_id, city_name, province_id, ...)

This is (one to many) relationship. It is not affected by the fact that more than one city have the same name. Therefore, in cities table, the combination (city_name, province_id) is unique. city_name by itself is not unique.

Improve this answer
4
  • what if I use a junction table?
    – The Man
    Commented Jun 2, 2016 at 19:19
  • Junction table, or "relation" table, is used for "many to many" type of relationship. For example, (student-course) relation where a student can take many courses, and a course can be taken by many students.
    – Jehad Keriaki
    Commented Jun 3, 2016 at 15:07
  • so in your solution the province and city has a identifying relationship?
    – The Man
    Commented Jun 3, 2016 at 16:09
  • I don't know what you mean by "identifying". However, a city is identified by both: its name and its province. The relationship type is "one to many". In other words, each city belongs to one province, and each province contains many cities.
    – Jehad Keriaki
    Commented Jun 3, 2016 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.