By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,964 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Mailing Addresses: Multiple addresses/address history?

P: n/a
For better or worse, I have chosen to implement mailing addresses in a
particular application as a separate table. One Person ==> Many Addresses.

It is a database for managing school reunions and people really do have multiple
(summer/winter, for instance) mailing addresses. I'd also like to keep track
of people's defunct addresses.

To that end, "tblAddress".
But I'm not sure how to handle address statuses/status dates....and even whether
I need some more address-specific fields besides AddressType.

Right off the bat, I need to identify addresses as current or defunct and keep
track of when an address was last verified or when it became defunct (a form of
verification, I guess...)

Maybe tblAddress ==> tblAddressAddressStatus ==> tlkpAddressStatus where the
link table has "Date" and "Remarks" columns?
--
PeteCresswell
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Can you get by with AddressType for summer/winter, for instance and
DateOfNewAddress? You could then use a query to return the type of address
you want and then return the most current address of that type using the
DateOfNewAddress field.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"(Pete Cresswell)" <x@y.z> wrote in message
news:7h********************************@4ax.com...
For better or worse, I have chosen to implement mailing addresses in a
particular application as a separate table. One Person ==> Many Addresses.
It is a database for managing school reunions and people really do have multiple (summer/winter, for instance) mailing addresses. I'd also like to keep track of people's defunct addresses.

To that end, "tblAddress".
But I'm not sure how to handle address statuses/status dates....and even whether I need some more address-specific fields besides AddressType.

Right off the bat, I need to identify addresses as current or defunct and keep track of when an address was last verified or when it became defunct (a form of verification, I guess...)

Maybe tblAddress ==> tblAddressAddressStatus ==> tlkpAddressStatus where the link table has "Date" and "Remarks" columns?
--
PeteCresswell

Nov 13 '05 #2

P: n/a
You have a table named person. You have a table named address. The primary
keys are person_id and address_id. Table address will have a foreign key to
table person, add_person_id. It will also have a field, add_start_date,
which is the date the address became a correct address. The newest start
date is the 'current address'.

Now, if you want to have separate mailing and physical addresses that are
both active, it becomes more complicated, but the principle is the same.
You'll just need to add a field add_end_date, so that you can easily tell
which address have not ended/are current.

The only problem with the above is that you'll almost always want the
current or primary address, and it's so much extra work to implement queries
that find the most recent start date for every person - so try this.

Add a foreign key field to your person table, per_prim_address_id, the
address_id of the person's primary/current address. This is much easier to
include in queries where you just want to print a list of addresses. Also
useful for per_prim_phone_id, for people with more than one phone number.
Darryl Kerkeslager

"(Pete Cresswell)" <x@y.z> wrote in message
news:7h********************************@4ax.com...
For better or worse, I have chosen to implement mailing addresses in a
particular application as a separate table. One Person ==> Many Addresses.
It is a database for managing school reunions and people really do have multiple (summer/winter, for instance) mailing addresses. I'd also like to keep track of people's defunct addresses.

To that end, "tblAddress".
But I'm not sure how to handle address statuses/status dates....and even whether I need some more address-specific fields besides AddressType.

Right off the bat, I need to identify addresses as current or defunct and keep track of when an address was last verified or when it became defunct (a form of verification, I guess...)

Maybe tblAddress ==> tblAddressAddressStatus ==> tlkpAddressStatus where the link table has "Date" and "Remarks" columns?
--
PeteCresswell

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.