PostgreSQL – Help with Sales Database Design

database-designpostgresql

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.

Best Answer

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).

Related Solutions

Database Design – Same Products in Inventory with Different Cost

So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

I think it is better choice to have of purchase_id as FK in SALE_ITEM table in oppose to product_id, because sell is something related to PURCHASE and PRODUCT both and this option offer you this relation, you can get the corresponding product_id and information related to PRODUCT by making a join in sql query.

Or, should I force the client to sell the old lot before selling the new ones?

Don't add restrictions with your design, let them give flexibility to choose the way they want to. Even this part can be handled with sql query incase needed.

Schema design for storing item combination with order

The design doesn't seem awful, but I think you could solve your update issue by changing how you are assigning a priority number. Instead of using 1, 2, 3, 4, ..., you could use a higher range between the values, such as 10, 20, 30, 40, .... You might find you need an even larger set like 50, 100, 150, 200, ....

By increasing the range, you have some flexibility when a user changes the ordering of a given item. Instead of having to update all of the records, you could now use an available number between the existing two and save yourself the headache. For example:

AAA -> 10
BBB -> 20
CCC -> 30

We want to move CCC to be the second item (in between AAA and BBB), so we change 30 to 15. So now:

AAA -> 10
CCC -> 15
BBB -> 20

Of course, you'll still have to re-assign all the priorities if the user changes too many of the items, as you'll eventually run out of empty numbers. When you're doing your selection, just remember to order by the priority.

Best Answer

Related Solutions

Database Design – Same Products in Inventory with Different Cost

Schema design for storing item combination with order

Related Question