Disclaimer: I am a beginner in the field of database.
I am trying to design a database in PostgresQL which will record the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM approach.
Here is the problem with the design.
This is the simplified description of my schema.
I have a Clients table, an ItemDetails table and a RateChart table, among others.
The Clients table records details for each client, including their address.
The ItemDetails table records details for each item we sell to our clients, including a short description for the items. We do update the description from time to time.
The RateChart table has the different rates for items for each client.
Now, I also have to save the details of each and every sale. So, I have an Invoice table, and InvoiceItems table.
The Invoice table saves the invoice number (primary key), the date, the total amount of the order and the client name(foreign key, refers to client name in client table).
The InvoiceItems table, on the other hand, records the items sold, the quantity of each item sold and the rate at which it is sold.
Now, here's the problem.
The items sold in InvoiceItems list is a reference to the ItemDetails table where details of all the items are saved. Now, the details of items are changed from time to time. For example, the description of items are changed. So, whenever I change those item details, those changes will cascade down to this InvoiceItems table, and will make the older invoice records erroneous, because that was not the description when the product was sold. Same problem lies with the Client field in sale table. Whenever I change an attribute any record in the client table, the changes will cascade down to the sale table.
Here is the schema for illustration.
Now, the solution I have thought of is highly inelegant. I have considered saving the client details for every sale in the Invoice table, and the item details in InvoiceItems. Of course, that would be a great deal of duplicate data.
Another solution might be to keep the defunct details of items and clients, which would mean polluting my ItemsDetails and Client table with records no longer valid. Of course, I can add a boolean IsValid column, but then again, the solution does not seem to be succinct.
Any help would be heartily appreciated.