We help IT Professionals succeed at work.

Database Design for Contacts & Addresses

My database has four categories of contacts: customers, vendors, general contacts and employees, each stored in a separate table.  I need advice on the most powerful method of managing addresses, some of which are US and some are foreign countries.  Is there a benefit to using dedicated table just for addresses or should I include addresses in each table. How can I best accommodate addresses from different countries?  How can I implement a zip code lookup?  
Comment
Watch Question

>Is there a benefit to using dedicated table just for addresses or should I include addresses in each table.  - Simpliest solution would be to create the address table and tie it to Autonumbered RecID in each of your named tables. This way you're only having to manage one set of data.

>How can I best accommodate addresses from different countries?  - IN your address table create all of the field, many will over lap, then on your form, you can hide/dispaly the required fields based on the country.

>How can I implement a zip code lookup?  Actually quite simple, create a bound field (bound to your zip-code field in your address table) and then create a separate table for tracking Zipcodes and associated city, state. Biggest issue wil be populating the zip code table. You can download an existing list and then create a Not-In-List event to capture new zips as your user enter news.

Hope this helps, Imoutwest
Left this out: the field should be a bound Combo-Box with the Zipcode table as its source. Limit to list set to yes, so you can add the new zips.
Commented:
Yes, i prefer to go for a dedicate table for contact address. just added a Type Field and a TypeId Filed
so if it is a address for a customer then in the table store the Type = "Customer" and the TypeId = CustomerId.

if you do this as a dedicated table, if any changes comes in future you can just alter this table.

Zip code look up, what you can do is subscript from sites like this https://www.zipcodedownload.com/
they will give you the csv with all details

Thanks
Dosth

Author

Commented:
Thanks.  These are helpful comments.  Is there a resource that provides information on international address formats?
Commented:
The question of how to store addresses is one of those conundrums that you have to deal with when designing your data model. Although having a separate address table with different address type is certainly an excellent solution but it might not always be the best solution. It all depends on how you use your data.

Dealing with a separate address tables with multiple addresses and address types can become quite complex and depending on your need you will have to build a lot of logic into your system that you might avoid with another design. For example, assume that you have multiple addresses and you need to contact a person, which address do you take? Do you specify one address as the primary address (but then you have to validate that every record has a primary address)? Or do you build some rules such as if one address is not defined take the next one and so on?

Another question you have to ask yourself is how detailed you want to be. For example, will need to break down your customers by city, state/region, country, etc. or do you simple need to know where to ship to in which case you could (and I am not saying this is the best solution) store your address as one long string.

What I am trying to get at here that there is no one solution fits all scenario. You have to look at your requirements and then try to match your requirements to that data model.

A good resource, although not free, is the book 'The Art of SQL' by Peter Robson and Stephane Faroult

Author

Commented:
mrihm - you have articulated my dilemma quite well.  My reason for posting this question was to gain insight into a problem that thousands of others must have faced.  I was hoping to learn how others have addressed (haha) this problem.  Although my application is not that unique, my design objectives place much importance on the ability to generate powerful report scenarios, therefore the more available data the better.  Thank you for your comments and the book reference.  I am really hoping to find a template that reflects all of the fields that a universal (international) address table might contain.  I can then build a very versatile UI (as Imoutwest suggested) to present the data in a user-friendly format.
If you are writing a multi country app then make sure all of your character fields are unicode.

So define them as NVARCHAR rather than VARCHAR.

This allows you to store every language in your database... japanese, arabic etc.