2

I am trying to build a database about locations like so:

  • Country → Region → Town

Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.

Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).

This is the best design I have come up with:

ER Diagram

The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.

The town column on its own is not UNIQUE as you may have the same town name in multiple places.

Is the towns table correct?

The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?

I would welcome feedback if this approach is correct.

Improve this question
6
  • town_id sounds like it should be the reference. Numeric (fast compare), auto_increment (ensures unique), and unique are all good properties one would want of a primary key. Perhaps I'm confused as to what's causing your confusion, given these properties.
    – Jaaz Cole
    Commented Jun 3, 2014 at 21:02
  • @JaazCole - sorry for any confusion, but are you suggesting town_id should be the primary key, or what I have proposed is best?
    – the_peacock
    Commented Jun 3, 2014 at 21:08
  • 1
    Based on what you described about the properties of town_id, I would recommend using it as the only primary key (provided my first comment is true), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names. You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.
    – Jaaz Cole
    Commented Jun 3, 2014 at 21:18
  • @JaazCole - haha ok great, this is where my knowledge let me down! i did not realise that you could add a unique index to apply to multiple columns as you have suggested (to country_id, region_id & town). This is exactly what I was trying to do. thanks for your help. do you want to post this as answer, or should i?
    – the_peacock
    Commented Jun 3, 2014 at 21:34
  • I added it, it's been fun.
    – Jaaz Cole
    Commented Jun 3, 2014 at 21:48

2 Answers 2

Reset to default
2

I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.

You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.

Improve this answer
-1

I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this enter image description here

Improve this answer

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.