什么更好？ DataBase上的国家，州和城市与否？

Question

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.

Answer 1

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.