Consider a UI website where a user could search with a hotel name or city. Entering a city would bring out all the hotels in that city. What will be the appropriate design for this problem. Basically it's a part of designing a system that allows for searching and booking of a hotel. But I am initially only looking for something more on database side. Example: One of the usecase will be a user searches for a hotel in "Dallas" or some other city our service should return the list of hotels
My Thoughts:
Approach-1:
Store the (lat, long) for each hotel in the table, and when a user enters a city, fetch the (lat, long) for the city and scan all the rows of the table and return rows with a distance less than a certain number. This will return hotels, say around 10 km of the (lat,long) of the city entered by the user. Although there is a possiblity that this may return hotels which are near to the boundary of the city and not exactly in the city. Which could be fine in some cases. I guess that's a decision that one has to make.
Approach 2:
Store the city, state and hotel details in a table. From the user input we can query like select * from hotels where city = {city}, state={state}
This can be optimized by building an index on the city and state columns. For this approach, a relational database like MySQL will be good enough.
I am not sure how proper are the design above and whether apart from a relational database some other NoSql database could be more effective. Are there any pros and cons for the above two approaches. Any alternate approach or modification of above two that could yield better results. Any comments whether mysql or any of NoSql databases will be good