I'll need to represent location of some events and I am designing database schema for this application. I have two approaches for presenting the location:
approach 1: 4 tables:
- Countries
- States
- Cities
- Locations (in location I have foreign key to country_id, state_id and city_id)
approach 2: 1 table:
- Locations and there are simply fields country, state, city which are stored as text (no foreign id's)
Which approach would you recommend? the first one will help to eliminate possible different names of e.g. the same country (usa, us, united states etc.) and could be helpful in providing suggestions when writing in textboxes which probably will be mandatory.
however, the second approach seems like it will allow keep everything much more simple and should reduce number of queries to the database.
Which one do you think is better? Do you know what are best practices in this case? E.g. how did it do some big portals where they also need something like location (e.g. foursquare etc.). Afaik facebook uses the second approach, but... I want to hear your opinions and possibly reasons why would you choose one approach over another.
Thanks!