-2

I'm building an application that deals with shipping (now in the US, but eventually internationally), and I'm trying to figure out the best canonical fields to user to store addresses in an SQL database.

My initial guess (with examples) is:

  • name_1 (Hulk Hogan)
  • name_2 (C/O World Wrestling Federation)
  • address_1 (12345 Suplex Ave.)
  • address_2 (Suite 420)
  • city (New York)
  • state (NY)
  • zip (10001)
  • country (USA)

So my questions are:

  1. Do these fields work for all addresses worldwide?
  2. Is there a standardized set of fields that I should be adhering to?
  3. Should name_1/name_2 and address_1/address_2 each just be a single field broken apart by newline characters?
philipxy
  • 14,867
  • 6
  • 39
  • 83
Bob
  • 92
  • 5
  • Is this really a SQL/Postgres question? – GMB Jan 09 '20 at 00:42
  • Feel free to suggest a better categorization/tag, but this is where I looked, and I'm sure the answer would be useful to others tagged as such. – Bob Jan 09 '20 at 01:19
  • Please ask 1 specific question per post. There's nothing DBMS-specific in the question. This is essentially asking for off-site resources, which is off-topic. It should also be closed as too broad. But it's also & nevertheless an easily found faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Jan 09 '20 at 08:58
  • After some more searching I found a [comprehensive answer here](https://stackoverflow.com/questions/929684/is-there-common-street-addresses-database-design-for-all-addresses-of-the-world). – Bob Jan 11 '20 at 03:50

2 Answers2

1

Concerning international addresses, I think that credit card fields would be an appropriate reference, since card users can be all around the globe (mastercard, visa).

In this link from paypal, they use the following fields:

For name:

  • First name
  • Last name

For address:

  • Street address
  • apt., std., bldg.
  • City, State, Zip Code.
wookiekim
  • 1,156
  • 7
  • 20
  • Interesting: In their code examples, at that link they refer to city and state as `admin_area_1 and `admin_area_2`. But if you search their docs some APIs call city and state `city` and `state`, while others call it `region` and `locality`. But in all of these cases the field usage is essentially the same. Just different field names. – Bob Jan 09 '20 at 01:22
  • Google "international addressing". The differences you mention is each country defines the convention for use within that country. – Belayer Jan 09 '20 at 02:10
0

The structure of postal addresses varies significantly from country to country. Worse still, in some (many?) countries addresses are not structured at all and look more like free-text directions, which only the local postman can understand.

If you must handle ALL addresses worldwide, you have to fall back on free form text.

If you can limit yourself to a subset of specific countries with structured addresses, you can come up with a set of fields which are a reasonable compromise between individual countries.

Michael Diomin
  • 530
  • 3
  • 13