Hi,

I have a field that I need to add to one of two tables. I was hoping to get a little input from the community before I add this field.

I have three tables that this issue deals with:
- a person table
- an address table
- a personaddress table (holds the person id and address id for the many-to-many relationship)

The field that I need to add is for a returned address. I want to add a checkbox Yes/No for a returned address, so that users can follow up on this when it occurs.

I would not add this field to the address table because the address may actually be valid for a person even if it is returnable for another person. My two choices as I see it are adding the field to the personaddress table (so it is attached to the particular person's address in question), or to the person table directly (so that this issue of a returnable address follows the person).

Both options would seem to keep my tables normalized, so my lean is to attach it to the person table, and the reason for this is that my perception is that for many-to-many tables I should only include the bare minimum of fields (personaddressID, personID, and addressid) and not clutter it up with additional fields.

Any best practices or other things that I should be aware of before adding this field?

Thanks

Actually, I would do it differently. I would have another table for mailings:

tblMailings
MailingID (PK Autonumber)
MailingDate
PersonAddressID (FK)
Returned

Thanks for your input, Scott

So, I'm going to play devil's advocate here...how would you reconcile that table to the mailing address in the personaddress table. With your suggestion, someone could still make another mailing to an invalid address using the mailing address in the personaddress table. I'm assuming here, and this is what I put into practice, that any mailing is using the mail-to address found in the personaddress table.

Can you give your reasoning why attaching the returned address marker to a distinct mailing in a mailing table is a better solution then attaching the returned address marker to the source data (i.e. the mail-to address).

First, I would prefer having a record of mailings. Using a mailing table will give you that. Second, you can easily eliminate returned mailings with a subquery. For example, you would have a query:

qryReturnedMailings
SELECT DISTINCT PersonAddressID FROM from tblMailings WHERE Returned = True;

Then in selecting address for mailing you would use:

SELECT PersonAddressID FROM tjxPersonAddress
WHERE PersonAddressID NOT IN(SELECT PersonAddressID FROM qryReturnedMAilings);

That gives you only valid addresses.

But the main reason is because it makes it easier ipdate the record, because you can pinpoint the mailing and find the mailing record more quickly in a smaller recordset.