Mysql – How to store Alternate Names for Places or City in Table

database-designMySQL

What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching?

Examples:
In India user may search for "Chennai" or "Madras" and both are same City.
And "Sanjeeva Reddy Nagar" or "SR Nagar" are same Locality in Hyderabad City that user may search for.

Properties
-----------
PropertyId INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
PropertyName VARCHAR(64),
City VARCHAR(32),
Locality VARCHAR(32)

So in above structure how shall we accomodate alternate names for City and Locality?

  • Option-1:

    May be having 2 more columns (AlternateCity, AlternateLocality) and have search function search in both the columns (Ex: if user search for Locality then it will search in Locality and AlternateLocality column)

  • Option-2:

    May be having the same column store multiple data values in one column. Ex: City can store "Chennai (Madras)" and search can use Like operator?

What is the best way to store these data knowing one place may have 2 – 3 names.

Thanks.

Best Answer

The normalised way would be create a master table for town and then create another table for alternate names with a 1 to many relationship. This allows you to have 0..N alternate names. To fully normalise it you would pull all the names out from the town table and put them into the town names table and have a flag which denotes the default or primary name and locality for that town or you could go even further and have a name type column allowing you to further define the name type in a meaningful way.

This happens a lot with names and addresses for people. People have a primary (or legal name) and they also have one or more aliases or other known-as names and the technique of separating the name from the actual person and categorising their name via a type is commonly used - the same for their addresses

Related Question