1

I run a hobby web site for tabletop gamers. They can search based on postal code in a radius round their location. As I consider adding new countries, my best option seem to be silo'ing the geo postal codes tables for each country instead of one large table in order to keep the length down. Canada alone is 91,8587 rows. By the time I add a few more english speaking countries, the table length could be a few hundred thousand.

However I was wondering if it would be smart to store the lat & lon in the user-list instead of normalizing the data where it would have to go through several JOINS. I can't imagine the user list would ever reach more than 50k. The user-list only has about 30 columns and is indexed.

Which is the better option?

edit: Forgot to mention that the web site does the radius search, not a 3rd party like google.

Improve this question
3
  • Can you add the design you are thinking about? I can't imagine why using postcode tables would require several joins.
    – ypercubeᵀᴹ
    Commented Jun 21, 2014 at 15:18
  • 1
    For the life of me, I can't understand why you would want to have different tables for different countries. Unless you're talking about partitions, I would do it in a single table.
    – Colin 't Hart
    Commented Jun 21, 2014 at 16:30
  • You wouldn't have a problem with 500k rows in a postal code table? I would think that searching for values that matched the radius around a lat & lon would take quite a while on something that large.
    – Maelish
    Commented Jun 21, 2014 at 19:17

1 Answer 1

Reset to default
0

I don't see why would't you just store the coordinates for every user. You can keep them either in the same table as users or in some kind of user details table, depending on how your current schema is designed. There is no point in having all geo to postcode locations mapped.

To get the locations you can use something like google maps API.

Improve this answer

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.