Data Modeling: hierarchy of geographic locations

I want my users to be able to specify their locations so that I can plot them on a map. Given an address, I use Google Maps API to get their lat/long coordinates and store that in the database.

Additionally, I want to allow users to searc开发者_运维问答h for other users based on location. Using Google Maps API, I can also get, say, country/state/city for address (or lat/long coordinates). My problem is that I don't know how to store country/state/city in such a way that:

• The data is associated to a particular user
• There is no data redundancy

The problem I think is if User-1 and User-2 both enter an address that results in the country being "USA", I think I need to know that User-1 and User-2 are both from the USA -- AND that "USA" is only stored once in the DB.

When users search for other users, I think I should only let them search for users in the USA if I actually have users from USA. Meaning, assume User-1 and User-2 are the only 2 users from the USA, if User-1 and User-2 delete their profiles, I shouldn't allow searches for users in the USA anymore.

Are my ideas conceptually wrong? In any case, how should I model this information? I'm using MySQL.

---

Your goals and intentions are correct (don't give them up!), you may need a bit of help getting over the line, that's all. The more understanding and experience you have with data modelling and Normalisation, the easier it will be. So do as much research and exercise as you can (SO or the web is not a good way to learn anything).

1. For loading and maintenance purposes, you are better off ensuring that you have a normalised, top-down structure for geographic locations. You can load it from info provided (usually free) by your council or county or Post Office or whatever. That will eliminate manual data entry for the External Reference tables.

   • This is a highly Normalised Data Model, at 5NF.

   • But there's more Normalisation that can be done; more duplication that can be removed. It is not worth it unless you are really interested, eg. you need to search on LastNames, etc.
     .

   • This one is for an Utility company, to ensure that false Street locations are not provided by the prospective customers.

   • Address is a specific house or unit (apartment), and that it not duplicated. Two People living at the same address will use one Address row.

   • This structure handles "any" geographic location. Note that some countries do not have State; some States do not have Counties; some Towns do not have Suburbs; etc. Rather than building all those exceptions into the Data Model, I have kept the hierarchy "clean". You will still need to handle that in your SQL (whether or not the model is simple with no exceptions, or whether it has exceptions; because that is the real world), and not display the State for a State-less Country. All you need is a row for the non-State with a StateCode of CHAR(0) that identifies the condition.

   • I have placed Longitude & Latitude at the Suburb level, assuming that that is what your users can choose easily via GoogleMaps, and because Street level will have limitation (might be to fine grained, and would cause duplication; or not fine grained enough for cities with very long Streets). Easy to change.

   • For now, I suggest you do not worry about identifying users in the same country, first see if you can handle the SQL to identify users in the same Suburb (not Street, that is easy). After that, we can deal with City, County, Country, etc.

   • I think the other searches you identify are effortless; see if you agree.

Anyway, this is just something to get you started; there is some interaction to be had before it is finalised.

Link to GLS Data Model (plus the answer to your other question)

Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.