I would like my customers to have the ability to specify their locations to ensure that I'm able to plot them on the map. Given a previous address, I personally use Google Maps API to have their lat/lengthy coordinates and store that within the database.
Furthermore, I wish to allow customers to find other customers according to location. Using Google Maps API, I'm also able to get, say, country/condition/city for address (or lat/lengthy coordinates). My issue is that I'm not sure how you can store country/condition/city in a way that:
- The information is connected to particular user
- There's no data redundancy
The issue I believe is that if User-1 and User-2 both enter a previous address that results in the united states being "USA", I believe I have to realize that User-1 and User-2 are generally in the USA -- Which "USA" is just saved once within the DB.
When customers look for other customers, I believe I ought to only allow them to look for customers in the united states basically really have customers from USA. Meaning, assume User-1 and User-2 would be the only 2 customers in the USA, if User-1 and User-2 remove their profiles, I should not allow looks for customers in the united states any longer.
Are my ideas conceptually wrong? Regardless, how must i model these details? I am using MySQL.
Your goals and intentions are correct (don't provide them with up!), you might need a little of help recovering from the road, that's all. The greater understanding and experience you've with data modelling and Normalisation, the simpler it will likely be. So just as much research and use as possible (SO or even the web isn't a great way to learn anything).
For loading and maintenance reasons, you're best making certain that you've a normalised, top-lower structure for geographic locations. You are able to load it from info provided (usually free) because of your council or county or Publish Office or whatever. Which will eliminate manual data entry for that Exterior Reference tables.
This can be a highly Normalised Data Model, at 5NF.
There is however more Normalisation that you can do more duplication that may be removed. It's not worthwhile unless of course you're really interested, eg. you have to explore LastNames, etc.
That one is perfect for an Utility company, to make sure that false Street locations aren't supplied by the mark clients.
Address is really a specific house or unit (apartment), which it not copied. A Couple living in the same address uses one Address row.
This structure handles "any" geographic location. Observe that some nations don't have Condition some States don't have Areas some Cities don't have And surrounding suburbs etc. Instead of building all individuals exceptions in to the Data Model, I've stored the hierarchy "clean". You still have to handle that inside your SQL (set up model is straightforward without any exceptions, or whether or not this has exceptions because that's the real life), and not display the Condition for any Condition-less Country. You just need a row for that non-Condition having a StateCode of CHAR() that identifies the problem.
I've placed Longitude &lifier Latitude in the Suburb level, presuming that that's what your customers can pick easily via GoogleMaps, and since Street level may have limitation (may be to fine grained, and would cause duplication or otherwise fine grained enough for metropolitan areas with very lengthy Roads). Simple to change.
For the time being, It is best to do not concern yourself about determining customers within the same country, first try to handle the SQL to recognize customers within the same Suburb (not Street, that's easy). Next, we are able to cope with City, County, Country, etc.
I believe another searches you identify are easy find out if you agree.
Anyway, case something to enable you to get began there's some interaction available prior to it being completed.
Link to GLS Data Model (as well as the response to your other question)
Link to IDEF1X Notation for individuals who don't know the Relational Modelling Standard.