标签云

微信群

扫码加入我们

WeChat QR Code

Given the task of storing international geographic addresses in a relational table, what is the most flexible schema? Should every part of the address be broken out into their own fields, or should it be more like free text?Is there any sense in separating differently formatted address into different tables? For example, have a table for USAAddress, CanadianAddress, UKAddress...?


schema.org/PostalAddress - used by search engines as standard

2019年09月18日30分36秒

262 types of streets? Might I ask how you obtained this information?

2019年09月18日30分36秒

Thomas - Lots and lots of searching and list keeping.Australia, UK, Ireland, Canada, USA, Channel Islands, France.It was an arduous task without getting each country's postal database.

2019年09月18日30分36秒

Thomas - Don't forget that in the English parts of the world, we often steal names from other countries - i.e. the US uses Spanish names in a lot of places and Canada uses French too.

2019年09月18日30分36秒

How do you handle requesting this information from users? Do you really have this many fields for them to read and decipher? I understand the need for it, but there is also a need for keeping data entry extremely simple for users, especially when tricky forms can lose you a sale or a new member.

2019年09月18日30分36秒

DeveloperChris The title of the question asks "How should international geographical addresses be stored in a relational database", so your comment is largely irrelevant in this context.

2019年09月18日30分36秒

You do raise a good point that I hadn't covered in my response.This is certainly something that should be considered when accounting for Dutch, German and other non-isolating languages.

2019年09月18日30分36秒

Address Line 1, 2 and 3 are definitely generic enough but when it comes to parsing addresses programmatically, you're going to be hosed.Parsing addresses programmatically is not a trivial task when considering international address formats.

2019年09月19日30分36秒

Stephen: +1, Not all countries have counties tho...

2019年09月18日30分36秒

Alix Axel - and for those countries, leave the field blank

2019年09月19日30分36秒

How come you've used Province but ZipCode?Also, the StreetAddress and StreetAddressLine2 are generic enough for display purposes but if you've gotta do EDI or parse the addresses programmatically for QA (or for any other reason) you're going to be up a gum tree.

2019年09月18日30分36秒

It simply depends what you need those data for. For sending post to customers worldwide my solution will be ok. For worldwide EDI you will probably need something like your answer to this question. However, for navigation purposes you will need additional data structures containing GIS data and links between them (so you will know that adress1 is located in the same place as adress2, even though that have got different street name, etc). So it is hard to say which solution is ok (not too complicated and accurate enough) without knowing the context.

2019年09月19日30分36秒