Address table design

[meckeard]

Hi All,

How does everyone else manage addresses in their database?

This always seems to change depending on the app. But I didn't know if someone had a multi-entity design that they found appropriate.

Anyone want to share their thoughts on their approach. Like how they designed the tables (high-level) and why they found it worked best for them?

Thanks,
Mark

 

[johnherman]

Some things to consider when designing addresses.

1. Addresses change over time. If you have a concern for previous addresses, then you may need to have an effective date field.

2. There are many different types of addresses: home, business, shipping, billing, mailing, vacation home, etc.

3. There can be multiple addresses of the same type for one customer. I once worked for an investment company where customers had multiple home addresses and *projected* dates of residence for those houses. For instance, the ski cabin in VT during January, the house in Bermuda during weekends in February, etc.

The extent to which these address "rules" apply to your business will assist you to determine how flexible you need to design your address tables.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[ceh4702]

Then there are the addresses of your children and wives which you may have several of due to divorce. You might need these for benefit forms or the like.

Sometimes all the possibilities are designed for and then not implemented due to the complicated procedure of input.

I have seen cases where the street numbers are separate from the street name. You might implement Zip Code Lookup to automatically stick in the Zip City and State so you get a better match. It is hell when the zip and the city/state do not match up. In a large city the street can be in more than one Zip Code, and the street numbers come in handy. For instance 3740 XX Road and 4560 xx road are on different blocks in different zip codes. I have never used the street numbers separate, but we use a Zip Code Lookup. It actually speeds up input.

Many times you need the county also. If you allow box numbers for mail there may be a need for multiple addresses when the Street address is required. Either that or always require a street address. Sending out a Check or a letter may have different requirements.

If you do not like my post feel free to point out your opinion or my errors.

 

[despierto]

I typically use "PostalCode" instead of "Zip Code" because internationally, most places have a postal code, Zip is an American concept. I also use "region" instead of "state" for the same reason.

Typically, I like to break address data into it's own table, and link it through another intermediary table, if I think there will be more than one address (the summer home example). I like the zip-city-state lookup table, but again, this only works for domestic US addresses. I'm sure that other countries have pc lookups that you could reference to build that.

The final answer, though, is what does the customer want? I HATE with triple capital letters having a "name" field instead of First Name, Last Name, but if the customer wants "Name" then that's what you build. So I don't think none of this helps you with your question of this always changes per app... Cause, yeah. It does.

 

[meckeard]

All good advice, but the design I need is a somewhat simple.

A customer will always be in the US, can have multiple addresses and will not have any child or relative addresses.

There are no "types" of addresses. Just one.

And companies will follow the same rules.

Thanks,
Mark

 

[johnherman]

Are you concerned with tracking the history of address changes? In other words, after the customer/vendor/employee moves, do you care about where he/she/it used to live?

If yes, then you need to have an effective date as part of the primary key for the address table.

If no, then you simply overwrite any old address data with new data whenever changes occur.

Cust/Vend/Emp table
Cust/Vend/Emp ID
Cust/Vend/Emp Name1
Cust/Vend/Emp Name2
etc

Address table
Cust/Vend/Emp ID
Effective Date
Location Name (optional)
Address Line 1
Address Line 2 (opt)
City
State
Country (opt)
Postal Code
General Contact Name (opt)


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright