0

I'm designing a MySQL database and a corresponding RoR app that will hold various businesses, each business will have an address.

A requirement of this application is to search the database by City/Country (this will hold businesses across Europe/UK). The search results will be returned by the nearest city in that country.

Here is a rough ERD: alt text

What is the best way to organize the DB? Should I move the city field into its own table? Should I store GPS coordinates for each business?

Thanks!

John Topley
  • 113,588
  • 46
  • 195
  • 237
christo16
  • 4,843
  • 5
  • 42
  • 53
  • What you have seems normal enough. If you want to locate businesses based on distance, you'll need the coordinates. – Stephen Nov 03 '10 at 18:12
  • is a search limited to a user's own country e.g. i live in London, search for a cheese company, your system suggests a company that's only 30 miles away but it's across the channel in FRANCE !! not much good for me really as i hate travelling - or would that be a VALID HIT in your system ? – Jon Black Nov 03 '10 at 18:31
  • @f00 thanks for the insight, we were planning on limiting it to country. So if you're searching in the UK, it will only return results nearby. – christo16 Nov 04 '10 at 04:00
  • ah good, then you might want to look at my solution here http://stackoverflow.com/questions/3983325/calculate-distance-between-zip-codes-and-users/3989830#3989830 – Jon Black Nov 04 '10 at 04:13

3 Answers3

2

I would store the City in a separate table with latitude/longitude of city hall (or city center). Then for each store I would store latitude/longitude.

This will allow you to calculate the distance from the store to city centre, and rank by distance, so that you can show the top N.

If later you store users' locations, then it is easy top moify your query to show distance from the user's location.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
1

You don't need to link to countryID from your Businesses table, since the PostalCodes table already has a FK to the Countries table.

dotariel
  • 1,584
  • 12
  • 23
1

I think having Countries and PostalCodes is overkill, would not bother. In your Countries table, have address, city, state, postalcode, country, lat, and lng.

Then use GeoKit to geocode your Address into Lat/Lng if your address is updated before_save.

Then you can be awesome and say things like (find all businesses within 10 miles of this address)

Business.within.find(:all, :origin=>'100 Spear st, San Francisco, CA', :within=>10)

Resources:

Jesse Wolgamott
  • 40,197
  • 4
  • 83
  • 109