Normalization/Optimization of Invoice Addresses
i have an invoicing solution that until recently performed an auto enter calculation to insert the client's address into the appropriate fields on the invoice. subsequently, the structure was changed so that the invoice table would not store address related information but would use a related record in an address table. however, if you do frequent work for the same client, you soon end up with dozens (or hundreds) of identical address records each related to a different invoice. can anyone recommend a method so that the address table would only need to store unique addresses but they could be linked to multiple invoices? also, this system would have to take into account that the address might be edited on one invoice (requiring the creation of a new address record) without changing the address on all of the other invoices related to the same address record? i considered using a join table but then isn't that even more overhead than just having redundant addresses? what is the best method to handle this problem? please advise... thanks!