Freezing a data element that may change downstream

[Bigdaddrock]

MS Access is great for relational databases. However, I have a unique problem from a client. Constructing a basic retail database, I had designed a customer table and an invoice table. The latter drew address info from the former table. However, my client is concerned that if a customer changes his address down the road, he will not have a record of where the original invoice was sent.
As such, he wants me to place the address data in both tables and "freeze" the info in the invoice table.
Any suggestions how I can limit the data entry, and bring the address info, at the time of creaing the Invoice record from the Customer table, and then "freezing it" so that it will not change if the Customer table gets updated at a later date.
Thanks for the assist.

[Jim Doherty]

MS Access is great for relational databases. However, I have a unique problem from a client. Constructing a basic retail database, I had designed a customer table and an invoice table. The latter drew address info from the former table. However, my client is concerned that if a customer changes his address down the road, he will not have a record of where the original invoice was sent.
As such, he wants me to place the address data in both tables and "freeze" the info in the invoice table.
Any suggestions how I can limit the data entry, and bring the address info, at the time of creaing the Invoice record from the Customer table, and then "freezing it" so that it will not change if the Customer table gets updated at a later date.
Thanks for the assist.

Depending on how sophisticated you want to be with the design of this the general principle should be that all structured physical addresses are stored in their own table and referenced by a Unique Primary key.

Yes I know many databases exist 'out there' where the Address1, Address2, Town, City, ZipCode fields and so on are kept in the customer table however, that type of design is essentially flawed for the very reasons you highlight. More often than not this invites one to copy over the data to the invoice table through some automated routine or other, thereby keeping statically stored data denormalised data throughout your system.

The primary key only of a structured address should be stored with a customer. It is logically correct that customers could have 'one' or indeed 'many' home addresses over time (or indeed many home addresses at the same time depending on how rich they are or how mobile they are)

The design of a system should reflect the capability to store one or many addresses against a person at any one time for the purposes of any individual transaction such as raising an invoice.

The primary key of any address table has an obvious relationship with customer table but the address table also has a relationship with the invoice table namely the address used for the invoice at the time the invoice is raised.

By keeping these entities separate at this level eradicates the problem as you experience.The invoice table should have the CustomerID and CustomerAddress ID (and maybe even DispatchAddress ID depending on your type of business need).

If this is a new build then the design needs to be looked at again. If this is a pre-built system at an advanced stage then your response may indeed be limited to forcing over individual address data from the customers table to the invoice table leaving the system design alone. . This is a simple enough affair but I feel it only right to point out what I consider to be the right way first.. to see if anything can be redeemed in the design

[Bigdaddrock]

Depending on how sophisticated you want to be with the design of this the general principle should be that all structured physical addresses are stored in there own table and referenced by a Unique Primary key.

Yes I know many databases exist 'out there' where the Address1, Address2, Town, City, ZipCode fields and so on are kept in the customer table however, that type of design is essentially flawed for the very reasons you highlight. More often than not this invites one to copy over the data to the invoice table through some automated routine or other, thereby keeping statically stored data denormalised data throughout your system.

The primary key only of a structured address should be stored with a customer. It is logically correct that customers could have 'one' or indeed 'many' home addresses over time (or indeed many home addresses at the same time depending on how rich they are or how mobile they are)

The design of a system should reflect the capability to store one or many addresses against a person at any one time for the purposes of any individual transaction such as raising an invoice.

The primary key of any address table has an obvious relationship with customer table but the address table also has a relationship with the invoice table namely the address used for the invoice at the time the invoice is raised.

By keeping these entities separate at this level eradicates the problem as you experience.The invoice table should have the CustomerID and CustomerAddress ID (and maybe even DispatchAddress ID depending on your type of business need).

If this is a new build then the design needs to be looked at again. If this is a pre-built system at an advanced stage then your response may indeed be limited to forcing over individual address data from the customers table to the invoice table leaving the system design alone. . This is a simple enough affair but I feel it only right to point out what I consider to be the right way first.. to see if anything can be redeemed in the design

Wow, thanks for the rapid response. I suspected that to be the case, but was hoping I was wrong. Ok, so since memory is not a factor (it is now so cheap) I am concerned with human error in keying in the street, city, state etc, twice for a customer - once into the Cust file and once into the Invoice file.
Any suggestions on how I can "autopopula te" those fields on the latter file's "new record" form, though giving the data entry person the ability to change it to reflect a different address?
The system is an old dbase IV database and I am bringing it up to MS Access.
Thanks again for your assistance.

[Jim Doherty]

Wow, thanks for the rapid response. I suspected that to be the case, but was hoping I was wrong. Ok, so since memory is not a factor (it is now so cheap) I am concerned with human error in keying in the street, city, state etc, twice for a customer - once into the Cust file and once into the Invoice file.
Any suggestions on how I can "autopopula te" those fields on the latter file's "new record" form, though giving the data entry person the ability to change it to reflect a different address?
The system is an old dbase IV database and I am bringing it up to MS Access.
Thanks again for your assistance.

If you take heed of he design approach I mentioned this should not be an issue. You should have a facility in your design (if you are not calling upon a prepopulated commercial addressing system) to create an address only ONCE. Once this address is created it should have a Unique primary key to identify it.

It is the Unique key you only ever use to populate tables. For instance inso far as your invoice is concerned you would create a query and have the invoice table in it. You then create a join between the invoice table and the Address table based on the unique key. You then drag into the grid the fields from the address table.

The same goes for a customer. When a query is created for a customer and you wish to store against them..... lets say it is there home address. You would (or should) have a link between a HomeID (foreign key) in the customer table and the primary key in the address table dragging into the grid appropriate fields from the address table. When you then run that query the relevant info becomes one and all you EVER do in the event of a change of home address for instance is change the homeID key value........th e rest is normal SQL functionality although in Access it is called "Autolookup " a fancy name for something that SQL has always done.