What do you think is the best balance of columns in an address table?

Question

Almost every time I create an application, I find that I am creating an address table containing addresses for the system.

What columns do you create “address” in your table (or tables, if you normalize addresses of more than one), and what is your reasoning behind this?

Answer 1

• line 1
• line 2
• line 3
• location
• region
• postcode
• country (which is a foreign key in the country table)

With the exception of the country, all of these fields are text (varchar).

This assumes that the recipient is stored elsewhere.

It may also be appropriate, for example, to lose one of the lines and replace it with:

• street number
• Street name

as it can help in checking, finding an address through web services, etc.

Answer 2

I'm going to toss the answer "it depends." If your application is simply related to the location of correctly typed address labels, a set of lines (line1, line2, line3, etc.) may be sufficient. A text block may also work, depending on how the data is entered. Give the user a box and let them type? Make them suitable in 3, 4, 5 lines? Whatever.

However, if you want to be able to "do things" with data, such as sorting by zipcode, analyze the distribution by city, state and / or country or track the number of digits at your street addresses (10 Main St. vs. 54321 Main St.) then you will need separate columns for each important piece of information.

It seems that the requirements will include “space for the address,” and decisions as to what actually happens to the addresses will appear later ... at this time they will want to be able to sort / count / expose / independently, even if they never will not do. According to the links provided in other messages, as soon as you reach the international level, it can become very complicated. I would say try and keep it as simple as reasonable, where “reasonable” depends on the business case for the requirements.

Answer 3

In an ideal world, each application will store addresses in a canonical format, such as IPN or BS7666. It is much easier to combine a structured address into a single line for printing than to subsequently extract address elements from a single block text. Because sooner or later someone, maybe not you, will want to “do something” with the address information. Data warehouses are very trendy these days.

Unfortunately, implementing something like BS7666 usually requires address verification software. It is unreasonable to ask an ordinary user for an address in the Procruste format: we cannot expect them to understand the difference between locality , a town and a post town . And it is misleading to have something in the standard format when it has not been verified as such.

But move on to some form of structure. Also, at the very least, check your regular expression zip / postal code to make sure it is in the correct format.