Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Design for Contacts & Addresses

Posted on 2007-11-02
8
Medium Priority
?
4,224 Views
Last Modified: 2008-01-10
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?  
0
Comment
Question by:dev775
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 7

Accepted Solution

by:
Imoutwest earned 800 total points
ID: 20205105
>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
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 20205110
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.
0
 
LVL 15

Assisted Solution

by:dosth
dosth earned 400 total points
ID: 20205671
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dev775
ID: 20205808
Thanks.  These are helpful comments.  Is there a resource that provides information on international address formats?
0
 
LVL 15

Expert Comment

by:dosth
ID: 20205847
0
 
LVL 5

Assisted Solution

by:mrihm
mrihm earned 800 total points
ID: 20205879
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
0
 

Author Comment

by:dev775
ID: 20205913
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20213122
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.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question