1

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.

enter image description here

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.

1

You have mentioned the two common solutions to this use case - point-in-time capture and history tracking.

Of the two, point-in-time capture is the more robust and easier for audit purposes. You capture exactly what was presented to the purchaser at the time they agreed to the sales contract. There is no argument about subsequent data creep. I have worked on a couple of enterprise implementations which adopted this approach. As a mental sop you can justify the duplication of values by saying the two tables have different semantics - the source is what is available and the destination is an event capture.

With history tracking you have to be absolutely rigorous ensuring every update or insert by every user, application, utility, ETL job, batch load and whatever, always, for the entire life of the system, sets the "from" and "to" dates, and nothing ever, ever deletes anything. This is hard (although temporal tables make it easier).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.