Address database schema
-
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world. My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country. In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
-
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world. My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country. In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
What do you mean by 'lowest element' ? I'd look at a few sites like Amazon, those guys have solved the issue of a generic address, I am willing to bet.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
-
What do you mean by 'lowest element' ? I'd look at a few sites like Amazon, those guys have solved the issue of a generic address, I am willing to bet.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design. When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq. In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq. Perhaps I'm just getting too picky!
-
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world. My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country. In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
My answer to your question is that there is no really elegant schema as by creating database fields one generalizes and there will be specific instances that do not fit into that generalization. Here is a schema I use and has worked well for years: Name, Address1, Address2, Address3, Region, ZIP, Country Because the schema is a generalization the work has to be done at the reporting level i.e. moving data up one line if a column contains a null value. Also the data is only as good as the people typing it in - so if someone types a country into the region column there is not much you can do.
You always pass failure on the way to success.
-
I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design. When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq. In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq. Perhaps I'm just getting too picky!
Not quite mate ;) Postcodes are seperate to locales (thornton). If you grab the data from auspost you'll find that while similar, postcodes can span multiple locales and locales can span multiple postcodes.
Mark Churchill Director Dunn & Churchill Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio. -
Not quite mate ;) Postcodes are seperate to locales (thornton). If you grab the data from auspost you'll find that while similar, postcodes can span multiple locales and locales can span multiple postcodes.
Mark Churchill Director Dunn & Churchill Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.Thanks - appreciate the info. I had a look at the data and there are indeed many instances where there is the same locality name spanning different postcodes, however, when you look at the reasons for their existance, then each one is actually a different physical location. In most cases this seems to be semantic although technically a different location. Examples are mainly for a large organisation to sort mail eg to differentiate say general mail, competition entry mail and perhaps parcels which are delivered to (most likely) the same building. I couldn't see any instance of a locale (if you include the delivery office and category + state) spanning multiple postcodes, although I have to admit I only scanned the data. I'll run some queries on it later and post the results, although I guess you have already done this at some point :-)