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
