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:
town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
town column on its own is not UNIQUE as you may have the same town name in multiple places.
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.