Looking for an opinion or a better solution. I have a profile site that I am creating and the members from my current site is close to hundred thousand already.
In my new system I am creating a geo map. Which only displays the countries, cities and regions of where members who have a profile from none of the other countries are displayed.
Here is how it works if you enter <!-- m --><a class="postlink" href="http://www.mydomain.com/geo/">http://www.mydomain.com/geo/</a><!-- m -->
It will display the following clickable map:
Usa
Canada
Australia
If you clicked <!-- m --><a class="postlink" href="http://www.mydomain.com/geo/usa/">http://www.mydomain.com/geo/usa/</a><!-- m -->
It will then display:
World
Usa
Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut and so on ...
The last of the map would the cities within the State.
So here is the opinion I am looking for, should I create the following geography table system and keep adding the geo region only when a user joins:
CREATE TABLE geography (
geo_id int(10) unsigned NOT NULL auto_increment,
country_id tinyint(3) unsigned NOT NULL default '0',
country varchar(64) NOT NULL default '',
region varchar(64) NOT NULL default '',
city varchar(64) NOT NULL default '',
PRIMARY KEY (geo_id),
UNIQUE KEY country (country,region,city),
KEY country_id (country_id),
KEY country_2 (country),
KEY region (region),
KEY city (city)
);
With the above here is an example if I only had one user from detroit, because detroit is the first to join it would be given the geo key 1.
geo_id would be 1,
country_id would be 1, ( this what is user to list countries in a certain order )
country would be usa,
region would be michigan,
city would be detroit.
And in the users profile table there would be a geo_id field which would get the value of 1.
I know that the geography table could get large because the world is quite large so is there a different approach I could take or does the above sound stable and efficient.
---------------------------
One other question:
If I use that or any other system there is bound to be someone would could spell there city wrong or even entering one the doesn't exist.
Is there a site where I can verify the geo region before I enter it into the table?
In my new system I am creating a geo map. Which only displays the countries, cities and regions of where members who have a profile from none of the other countries are displayed.
Here is how it works if you enter <!-- m --><a class="postlink" href="http://www.mydomain.com/geo/">http://www.mydomain.com/geo/</a><!-- m -->
It will display the following clickable map:
Usa
Canada
Australia
If you clicked <!-- m --><a class="postlink" href="http://www.mydomain.com/geo/usa/">http://www.mydomain.com/geo/usa/</a><!-- m -->
It will then display:
World
Usa
Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut and so on ...
The last of the map would the cities within the State.
So here is the opinion I am looking for, should I create the following geography table system and keep adding the geo region only when a user joins:
CREATE TABLE geography (
geo_id int(10) unsigned NOT NULL auto_increment,
country_id tinyint(3) unsigned NOT NULL default '0',
country varchar(64) NOT NULL default '',
region varchar(64) NOT NULL default '',
city varchar(64) NOT NULL default '',
PRIMARY KEY (geo_id),
UNIQUE KEY country (country,region,city),
KEY country_id (country_id),
KEY country_2 (country),
KEY region (region),
KEY city (city)
);
With the above here is an example if I only had one user from detroit, because detroit is the first to join it would be given the geo key 1.
geo_id would be 1,
country_id would be 1, ( this what is user to list countries in a certain order )
country would be usa,
region would be michigan,
city would be detroit.
And in the users profile table there would be a geo_id field which would get the value of 1.
I know that the geography table could get large because the world is quite large so is there a different approach I could take or does the above sound stable and efficient.
---------------------------
One other question:
If I use that or any other system there is bound to be someone would could spell there city wrong or even entering one the doesn't exist.
Is there a site where I can verify the geo region before I enter it into the table?