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.
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.