What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching?
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?
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)
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.