What's better? Countries, States and Cities on DataBase or not?

I'm developing a website with PHP & MySQL where users can sign up. In the sign up form they need to fill up country, state and city. I want to know if it's a good idea to save it on my database or not. I'm confused because Facebook, google, and others have autocomplete forms where those input tags suggest you country, state and开发者_运维技巧 even cities.

---

You should not save all three separately, like country_id, state_id and city_id. The reason being that this is redundant information. A city implicitly belongs to a state belongs to a country. You are not gaining any information by saving them as separate values, and are only introducing the risk of messing up the data (since you could save Japan, Washington, Berlin as a set, which is invalid and inconsistent information).

You should create an hierarchical tree in your database. Cities have a parent state have a parent country. Then you only need to ask the user for their city and all the other information is implicit. Where you get that data from is a different topic. You can try Google's API, for instance.

---

Question is whether you want to make it work for everybody in the world, or just for US addresses -- and also for what purpose you are trying to gather the information.

If your site is ecommerce and you want to collect shipping addresses, you will need to have a fairly good quality of validation of your input -- otherwise UPS will not deliver the stuff to your customers.

If on the other hand, you just want to have a fairly good idea of where people are, but that it is not essential to have postal address quality, just use a free web service like PlaceFinder

http://developer.yahoo.com/geo/

It should allow you to let your users to just type in the address without splitting it up into city/state/country and just store the resulting place ID (woeid) in your database.

---

Generally, I'd recommend you to have read-only list of Countries, so user can only choose but not add. Then, for states and cites, have a pre-loaded list of them, but allow the users to add one. (You can preload this lists from any internet database or service you want, like yahoo's).

In all cases, suggesting is a very good plus, for them (because of usability) and for you (because it will reduce data duplication)

Hope this helps. Cheers

---

Also take into account, that there are cities which have the same name but are located in separate countries. Riga is a capital city of Latvia and also Riga is in USA, Michigan.

---

That depends on the requirements of your business. If you want to offer regionalized services (having logc based on the geographic region of users) then yes, you should definitely hold the regions in the DB.

On the other side, you will have to design your DB to hold your regions (with at least 3 tables in 1-n relationship or 1 table with parent/child relationship) and fill that DB with valid data - do you have all the countries/states and cities from all over the world?

---

CREATE TABLE `pl_countries` (
  `countryID` int(4) NOT NULL auto_increment,
  `iso` char(2) NOT NULL,
  `name` varchar(80) NOT NULL,
  `printable_name` varchar(80) NOT NULL,
  `iso3` char(3) default NULL,
  `numcode` smallint(6) default NULL,
  `top_flag` tinyint(1) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY  (`countryID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `cities` (
  `city_id` bigint(20) NOT NULL auto_increment,
  `countryID` bigint(20) NOT NULL,
  `city` varchar(30) NOT NULL default '',
  `top_city` tinyint(1) NOT NULL,
  PRIMARY KEY  (`city_id`),
  KEY `country_id` (`country_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Use the above structure for city to country mapping. You can also mark top cities and countries.