Best ways to store address history

[andy_25]

Hi all,

I have the following table in my database:

INVOICE (InvoiceNo, ClientNo, InvoiceDate, CustOrderNo, CustRef)
I obviously have an INVOICE_LINE table that goes with it.

When I update the CLIENT table and then reproduce an Invoice it will take on the new address. I want to store the address for the client at time of invoice production. Could someone suggest a table design to do this?

There will also be an issue if the CLIENT is deleted all together.

Any thoughts?

 

[DCrake]

Once a client is on the system and an invoice has been created for them, then there is no reason why you would need to delete them. If you no longer trade with them or for soem reason do not want them to be available for selection then simply have a field in the table for active/dormant and flag accordingly. Deleting them ould result in losses in referential integrity.

What percentage of times do clients change their addresses? in my mind this is a very low percentage, you may be building a rod for your own back for the sake of the odd percentage. If it worries you that much, then store the billing address in the invoice header table. Ok this seems like non normalisation but will cover you issue.

 

[Rabbie]

A quick legal point.

In the UK it is a legal requirement to keep all tax records like invoices for 7 years. his is probably a requirement in many other countries

 

[Simon_MT]

The legal requirement in the Uk is to keep a paper version of the invoices. The fail safe way to retain the correct information is to store the address within the invoicing system. The problem here is that if the Client has changed address there maybe some confusion if the Invoice Address is different to that of the Clients. So a compromise maybe to print out all the invoices and accept some discrepancies within the system and all Invoices can be identified with the Client.

The only time it is really neccesary to hold a different address is when the Invoice address is different to that of the Client table.

Simon

 

[andy_25]

I agree with all points made. I think I will note this as a shortcoming of the database and move on. I do not want to bloat the database just for the odd client address change (which will happen). This system is not for accounting purposes so maybe it will be picked up by the accountancy system anyway.

Another note to raise; I have got the same problem with the PURCHASE ORDERS table....

The delivery address will (99% of the time) be where I have a contract taking place and therefore will be accessible by a query. However the other 1% of the time I will need to manual input it.

Can anyone see a way around storing the delivery address in the PO table for the sake of 1% of POs raised?

 

[andy_25]

The only time it is really neccesary to hold a different address is when the Invoice address is different to that of the Client table.
Simon

As with the Purchase Order example I have just given. I do not want to put the delivery address details in the PO table for the sake of 1% of orders. Any ideas how to get around this? Linking to a PO_Delivery table to store the address etc?

 

[Simon_MT]

In this case I have a separate table, rarely used as you indicate and use a subreport to test if there is information in this table for a particular PO, otherwise use the Supplier Address. I would leave the main PO form as is and create a popup dialog form to handle the differing address.

Simon

 

[boblarson]

My suggestion would be to use a junction table for the address and use a subform for entry. You can add as many addresses as are required for this person/company and have a checkbox to select which is the active one.

You would then store the CompanyAddressID in the table with the invoice or PO. History is maintained and only the addresses required are stored.

 

[AccessBlaster]

From a purely novice point of view. Since this occurrence only happens about 1% of the time, I would note the change (old address) in a memo field and be done with it.

 

[boblarson]

From a purely novice point of view. Since this occurrence only happens about 1% of the time, I would note the change (old address) in a memo field and be done with it.

And as a professional Access Developer, I would suggest the junction table approach (as I posted above) as you store just what is needed and in a properly normalized and easily retrieved format.

 

[Lightwave]

My suggestion would be to use a junction table for the address and use a subform for entry. You can add as many addresses as are required for this person/company and have a checkbox to select which is the active one.

You would then store the CompanyAddressID in the table with the invoice or PO. History is maintained and only the addresses required are stored.

This is the gold standard but all the mobile phone apps I've ever had and Outlook (03 at least) use quick and durty method!!!

One of the reasons why I don't always like them.

 

[andy_25]

My suggestion would be to use a junction table for the address and use a subform for entry. You can add as many addresses as are required for this person/company and have a checkbox to select which is the active one.

You would then store the CompanyAddressID in the table with the invoice or PO. History is maintained and only the addresses required are stored.

Thanks Bob, that is exactly what I was looking for. I knew there would be a better way than to simply store the address in the table in an unnormalised form.