The Integrity of linked data entities on update

Question

What is the best-practice for maintaining the integrity of linked data entities on update?

My scenario

1. I have two entities "Client and Invoice". [client is definition and Invoice is transaction].
2. After issuing many invoices to the client it happens that the client information needs to be changed e.g. "his billing address/location changed or business name ... etc".
3. It's normal that the users must be able to update the client information to keep the integrity of the data in the system.
4. In the invoice "transaction entity" I don't store just the client id but also all the client information related to the invoice like "client name, address, contact", and that's well known approach for storing data in transaction entities.
5. If the user created a new invoice the new client information will be stored in the invoice record along with the same client-id (very obvious!).

My Questions

1. Is it okay to bind the data entities "clients" from different locations for the Insert and the update? [Explanation: if I followed the approach from step 1-4 I have to bind the client entity from the client table in case of creating new invoice but in case of updating/printing the invoice I have to bind the client entity from the invoice table otherwise the data won't be consistent or integer...So how I can keep the data integrity without creating spaghetti code in the DAL to handle this custom requirements of data binding??]
2. I passed through a system that was saving all previous versions of an entity data before the update "keeping history of all versions". If I want to use the same method to avoid the custom binding how I can do this in term of database design "Using MYSQL"? [Explanation: some invoices created with version 1.0 of the client then the client info updated and its version became 1.1 and new invoices created with last version...So is it good to follow this methodology? and how I should design my entities/tables to fulfil the requirements of entity versioning and binding?
3. Please provide any book or reference that can kick me in the right direction?

Thanks,

Answer 1

I'm not completely clear on what you are getting at, but I think you want to read up on normalization, available in many books on relational databases and SQL. I think what you will end up with is two tables connected by a foreign key, but perhaps some soul-searching per previous sentence will help you clarify your thoughts.

Answer 2

What you need to do is leave the table the way it is. You are correct, you should be storing the customer information in the invoice for history of where the items were shipped to. When it changes, you should NOT update this information except for any invoices which have not yet been shipped. To maintain this type of information, you need a trigger on the customer table that looks for invoices that have not been shippe and updates those addresses automatically.

If you want to save historical versions of the client information, the correct process is to create an audit table and populate it through a trigger.

Data integrity in this case is simply through a foreign key to the customer id. The id itself should not ever change or be allowed to change by the user and should be a surrogate number such as an integer. Becasue you should not be changing the address information in the actual invoice (unless it has not been shipped in which case you had better change it or the product will be shipped to the wrong place), this is sufficent to maintain data integrity. This also allows you to see where the stuff was actually shipped but still look up the current info about the client through the use of the foreign key.

If you have clients that change (compaies bought by other companies), you can either run a process onthe server to update the customer id of old records or create a table structure that show which client ids belong to a current parent id. The first is easier to do if you aren;t talking about changing millions of records.

Answer 3

"This is a business case where data mnust be denormalized to preserve historical records of what was shipped where. His design is not incorrect."

Sorry for adding this as a new response, but the "add comment" button still doesn't show.

"His design" is indeed not incorrect ... because it is normalized !!!

It is normalized because it is not at all times true that the address corresponding to an invoice functionally depends on the customer ID exclusively.

So : normalization, yes I do think so. Not that normalization is the only issue involved here.