CODEWITHSUNDEEP
mysqlnormalization
Steven
Steven

Reputation: 19455

What is the best normalization for street address?

Today I have a table containing:

Table a
--------
name
description
street1
street2
zipcode
city
fk_countryID

I'm having a discussion what is the best way to normalize this in terms of quickest search. E.g. find all rows filtered by city or zipcode. Suggested new structure is this:

Table A
--------
name
description
fk_streetID
streetNumber
zipcode
fk_countryID

Table Street
--------
id
street1
street2
fk_cityID

Table City
----------
id
name

Table Country
-------------
id
name

The dicussion is about having only one field for street name instead of two.
My argument is that having two feilds is considered normal for supporting international addresses.

The pro argument is that it will go on the cost of performance on search and possible duplication.

I'm wondering what is the best way to go here.

UPDATE

I'm aiming at having 15.000 brands associated with 50.000 stores, where 1.000 users will do multiple searches each day by web and iPhone. In addition I will be having 3. parties fetching data from the DB for their sites.

The site is not launched yet, so we have no idea of the workload. And we'll only have around 1000 brands assocaited with around 4000 stores when we start.

Upvotes: 4

Views: 3350

Answers (4)

Matt
Matt

Reputation: 23789

As others have mentioned, address normalization (or "standardization") is most effective when the data is together in one table but the individual pieces are in separate columns (like your first example). I work in the address verification field (at SmartyStreets), and you'll find that standardizing addresses is a really complex task. There's more documentation on this task here: https://www.smartystreets.com/Features/Standardization/

With the volume of requests you'll be handling, I highly suggest you make sure the addresses are correct before you deploy. Process your list of addresses and remove duplicates, standardize formats, etc. A CASS-Certified vendor (such as SmartyStreets, though there are others) will provide such a service.

Upvotes: 0

Pekka
Pekka

Reputation: 449783

I think the topmost example is the way to go, maybe with a third free-form field:

name
description
street1
street2
street3
zipcode
city
fk_countryID

the only thing you can normalize half-way sanely for international addresses is zip code (needs to be a free-form field, though) and city. Street addresses vary way too much.

Upvotes: 1

Michael Durrant
Michael Durrant

Reputation: 96594

My standard advice (from years of data warehouse /BI experience) here is:
always store the lowest level of broken out detail, i.e. the multiple fields option.

In addition to that, depending on your needs you can add indexes or even a compound field that is the other two field concatenated - though make sure to maintain with a trigger and not manually or you will have data syncronization and quality problems.
Part of the correct answer for you will always depend on your actual use. Can you ever anticipate needing the address in a standard (2-line) format for mailing... or exchange with other entities? Or is this a really pure 'read-only' database that is just set up for inquiries and not used for more standard address needs such as mailings.

A the end of the day if you have issues with query performance, you can add additional structures such as compound fields, indexes and even other tables with the same data in a different form. Then there are also options for caching at the server level if performance is slow. If building a complex or traffic intensive site, chances are you will end up with a product to help anyway, for example in the Ruby programming world people use thinking sphinx If query performance is still an issue and your data is growing you may ultimately need to consider non-sql solutions like MongoDB.

One final principle that I also adhere to: think about people updating data if that will occur in this system. When people input data initially and then subsequently go to edit that information, they expect the information to be "the same" so any manipulation done internally that actually changes the form or content of the users input will become a major headache when trying to allow them to do a simple edit. I have seen insanely complicated algorithms for encoding and decoding data in this fashion and they frequently have issues.

Upvotes: 2

codymanix
codymanix

Reputation: 29540

Note that high normalisation means more joins, so it won't yield to faster searches in every case.

Upvotes: 1

Related Questions