@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 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 CustomerAddressID (
and maybe even DispatchAddressID 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