If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design theory for maintaining historical address communications
I was curious to get some ideas for the best way to maintain the address of
mailers that had been sent out. So by way of example we send out mailers or invoices periodically. And lets say they go to Joe Smith at 1 place last quarter. Now when Joe Smith changes his address we then send it to 2 Other Place. Originally I had designed the database to have the linking table with the invoice number and the client contact ID, but the issue with that is that when Joe Smith changes his address it appears that we sent the invoice last quarter to the new address. Theoretically, what do you guys think is the best way to manage this. I could copy the address into the linking table and get rid of the contact ID. Or create a new contact id and copy the address and person into a new table that basically represents that period's address information. So Joe Smith would be in this table twice, once with old address with the last quarter date and once with new address and this quarter's date and the linking table would point to the ID for these. What do you think? |
#2
|
|||
|
|||
Design theory for maintaining historical address communications
Here's another approach ...
If you have an [Address] table and a [Person] table you can use a [PersonAddress] (junction) table to show which person at which address (using the primary keys of each). If you then add a FromDate and a ToDate field to that junction table, you can end-effective date Joe's old address and begin using the new address (actually, the new AddressID). If you need to keep history of which address was used to mail objectX, include the AddressID in your record of mailing. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "icccapital" wrote in message ... I was curious to get some ideas for the best way to maintain the address of mailers that had been sent out. So by way of example we send out mailers or invoices periodically. And lets say they go to Joe Smith at 1 place last quarter. Now when Joe Smith changes his address we then send it to 2 Other Place. Originally I had designed the database to have the linking table with the invoice number and the client contact ID, but the issue with that is that when Joe Smith changes his address it appears that we sent the invoice last quarter to the new address. Theoretically, what do you guys think is the best way to manage this. I could copy the address into the linking table and get rid of the contact ID. Or create a new contact id and copy the address and person into a new table that basically represents that period's address information. So Joe Smith would be in this table twice, once with old address with the last quarter date and once with new address and this quarter's date and the linking table would point to the ID for these. What do you think? |
#3
|
|||
|
|||
Design theory for maintaining historical address communications
IMHO you have a fundamental "entity: issue. The invoice, with all of it's
then-current details, is an entity that you created and need to look back at. In that case you would probable need an "invoices" table (probably a pair of linked tables, the header information and the detail information. |
#4
|
|||
|
|||
Design theory for maintaining historical address communication
I do have an invoices table, are you thinking that there should be the
address for each invoice created or if thre are two addresses a secondary table that would have invoice id and the address so it is kept like that? And each period addresses are written to this table as invoices are created? I was trying to clarify what you said to see if i understood. thanks "Fred" wrote: IMHO you have a fundamental "entity: issue. The invoice, with all of it's then-current details, is an entity that you created and need to look back at. In that case you would probable need an "invoices" table (probably a pair of linked tables, the header information and the detail information. |
#5
|
|||
|
|||
Design theory for maintaining historical address communication
First, to clarify, my approach is very different than Jeff's. If the only
thing (that you need to be able to look back on) that will ever change is the address, then I think that Jeff's way is better. Then ther is the low tech way of doing what I was saying, which would be to print and file the invoice. To do the electronic way of what I was saying, by software or user practices, you should make sure that an invoice is treated as a one time event. After that you are only producing COPIES of the invoice. Then under my way, you would make an "InvoiceHeader" table which has all of the "once per invoice" data, exactly as printed. (except for items mathematically calculated form items on the invoice) mathematically calculated items) And then the same as the above with an "InvoiceItems" table. |
Thread Tools | |
Display Modes | |
|
|