Creating an efficient database system

admin

Administrator
Staff member
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?
 
Back
Top