0

I'm using Google to geocode addresses, which breaks down an address into geographic units like country, state, city, etc. Depending on the address being geocoded, some geographic units may or may not be returned (e.g. maybe city is not returned, but suburb will be).

I'm tempted to store the data of each broken down address in a single row of a table, like:

USA, California, San Francisco
USA, California, San Jose
USA, California, Los Angeles

Obviously, this doesn't seem normalized as data is being repeated. I still could potentially query this effectively, though (e.g. list all states for USA).

Another option would be to save the data in parent-child relationships, a tree. That would be more normalized, but potentially harder to maintain and query (I can use a library to make this a lot easier, though, so it's not much of a concern for me).

My biggest concern using a parent-child model is that the branches may not all be the same length (e.g. city is not returned, but instead suburb is after the state). If I were to query for all the nodes below the state level, I could get a mix of cities and suburbs -- which might not be ideal. I could avoid this problem if I saved the data in a single row, with each geographic unit in their appropriate columns.

What's the proper way to model address parts?

1

Depending on what country you are in the rules for addresses (especially postal addresses) can get pretty dicey. For example, you're pretty safe to assume that a zip/postal code has one official city name, but both the U.S. and Canada allow for alternative city names for a postal code. I know this for a fact because I used to develop postal address validation software for North America. The non-official names are often recognized by the postal authorities and you typically have to permit their use.

Outside of U.S. / Canada the rules can get even more loose. There are not that many countries with postal authorities that standardize addresses in a fairly hierarchical structure (U.K., Germany, Australia) so in a lot of countries you can't even count on that.

The difficulty with normalizing address data is that, as you've already seen, it is somewhat hierarchical, but inconsistent across different geographies. Japan has prefectures, Canada has provinces and territories, the U.S. has states, districts, territories and commonwealths. Apart from naming conventions, in different places you may skip (or add) entire levels of geography.

You're thinking of the problem from the wrong angle.

You're instinct to be wary of redundant data is a good one. In transactional systems you should always try to start with data that's in third normal form (3NF) at least. This helps you to avoid insert, update and delete anomalies. In other words, the problem with redundant data is that it can become inconsistent when you start changing it.

In your case, however, all of your geographic data is coming from a Google API. If you change an address, I presume you're going to go back to the Google API and refresh the whole thing as a unit. You wouldn't go in and selectively change one element in an address because it had been renamed, for example.

Normalization doesn't especially help you because you aren't manipulating data elements within the address, you're always working with the address as a whole.

In your case, I would recommend leaving address elements classified in columns according to what the Google API says they are. Don't try to build trees because they will be hard to work with and the usual reasons for wanting to represent your data in a tree don't apply to your situation.

6
  • You are correct. If an address is changed, another call to Google API will be made to populate those columns. Currently, the only reason for storing that data is so that I can do queries like "return all records where country is USA and state is California" or "return list of states for country USA". Oct 14 '18 at 12:12
  • Of course, with your proposed solution, I couldn't "return a list of all states for country USA". I would need to have a record with an address in all 50 states to get a complete list ... Oct 14 '18 at 12:16
  • With a tree, I could store the address (just the address, not the parts) in a table and then have a foreign key referencing one of the leaf nodes in the tree of geographic units??? Oct 14 '18 at 12:27
  • 2
    @DatabaseNewbie How do you get a list of all 50 states to begin with (plus DC, PR, Guam, etc., etc.) if your address data comes from Google geocoding APIs? If you don't have any addresses in Iowa, does your system care that Iowa is a state in the U.S.? Trees won't work well for addresses because they aren't balanced, plus they're more like networks, than trees. You'd need an unbalanced tree solution, such as an adjacency list to handle this, which is difficult to work with in SQL so you aren't saving any trouble or effort in querying your data. The opposite would be true, in fact.
    – Joel Brown
    Oct 14 '18 at 12:37
  • 2
    @DatabaseNewbie - Creating a canonical list of every possible value for something is a different problem from creating distinct lists of (current) actual values. You can find free and paid canonical lists of geographical hierarchies. If you did that though, you'd have a new problem: how do you map your canonical lists to whatever it is that the Google geocoding API produces. One problem isn't necessarily better than the other. It just depends on what your system's objectives are.
    – Joel Brown
    Oct 14 '18 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.