CODEWITHSUNDEEP
database-designdatabase-normalization
Splashlin
Splashlin

Reputation: 7585

Normalization of Address Dealing With Multiple Companies Address Formats

I'm building a platform that integrates with 20+ vendor APIs in regards to a location specific product. As we've begun building the product, we've noticed that the locations we get back from some vendors differs from others for the same address. Also, the same address may have different phone and/or emails associated with it vs other partners DBs.

Ex. 5th W St or 5th West St is the same address; but, recorded differently by vendor OR 107 Maple Dr has phone 555-555-5555 and 444-444-4444 from a different vendor

I'm curious in the best way to setup a DB that can handle the specific differences and relate them all back to a master table of locations/phones/emails that we have internally. In order for us to make the correct calls back to them though, we have to convert our address/phones/emails to their exact format. I feel there's got to be a best practice in regards to normalization here versus storing 20+ different address tables. Any thoughts would be greatly appreciated.

Upvotes: 0

Views: 68

Answers (2)

Alain Collins
Alain Collins

Reputation: 16362

Unless this will be your core competency, use a third party. If you don't mind violating google's TOS, their mapping API will correct the address. Or use a commercial product, like this: http://smartystreets.com/products/liveaddress-api

Upvotes: 1

user3708429
user3708429

Reputation: 13

Personally, I would make some sort of reference where I know "W" and "WEST" are the same, so when I import data, if it's West instead of just W, then I would change it. There aren't really THAT many different ways to write street names, so if you break them up enough it shouldn't be that bad of a task.

I'd seperate them into: Number, Direction (N,S,E,W), Name, Type? (not sure what to call this one) (Street, Road, Blvd, ect), and as I read them into the "master address table", change them to what I want them to be.

That way you can check for duplicates and have everything in the same format.

Upvotes: 0

Related Questions