0

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.

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
7
  • Thanks @James for the answer, but in our case it will be a single denormalized table that the API will be hitting to so we need to think if we really need to go for another master/detail table.
    – Prabhat
    Commented Mar 31, 2016 at 12:27
  • 2
    You can expose the able via the API in a denormalised way. It would be very simple to concatenate the various entries to give you a single line result string.
    – James Culshaw
    Commented Mar 31, 2016 at 13:06
  • 1
    What the database does needn't be (shouldn't be!) exposed to the API.
    – Phil Sumner
    Commented Mar 31, 2016 at 15:28
  • In my scenario modifying API or having a master detail table are not the Options. So I will have to think of having the data stored into the single table only. I know it is not good to store in same table but in some situation we need to adjust as needed.
    – Prabhat
    Commented Mar 31, 2016 at 17:09
  • 1
    @Prabhat if you don't have a great degree of flexibility then I would go with option two if you are likely to need more than one alternate. You will have to keep an eye on the performance of the like query. If you structure the data properly and use some form of delimitation you could split the values into a temp table which may be worth exploring if there is lots of alternate names per town.
    – James Culshaw
    Commented Mar 31, 2016 at 21:30

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.