I have a question about how to structure the “Sales” data type.
The issue is the following: “Sales” includes a list of products. Those products can have a particular discount in each sale.
I don’t know if I should create a new data type that is “sales products” and that has 2 fields: product + discount; or if it is better that “Sales” includes a list of products and in the RG include a “discount” input and calculate in each cell the price after discount. The problem with this is that I don’t know how to get the total with discount from “Sales”. Is it possible to extract from each cell of the RG the value of the product post discount if I create an input that makes value product*(100%-discount%)?
Since the discount can change depending on a particular “Sale” of a Product then by definition it is NOT an attribute of the product, but rather an attribute of Sale.
Sale actually has two parts… The Sale table , and second the table of items sold. This means that you actually do need two tables, one that is Sale which includes a “thing” called something like Item Sold pointing to a table that is called Item Sold (which has two things, one is Product which points to the associated product table and one that is Discount - which is the specific discount applied to that specific item sold for that particular sale. But… add to this same table other fields such as Quantity Sold, Unit Price, and Item Total.
Unit Price stored here prevents the price at time of the sale from changing in the future if you change the Unit Price of the underlying product.
Now… this is where some may disagree! When you create the Sale Item record calculate the Sale Item line Total at that time and store it. Yes… it consumes 8 bytes of storage for each item sold BUT it significantly simplifies all the math and UI work in the future. FYI you should also round it to two decimal places at this point in time.
My suggestion is that you do a calculation in one place, and only once if you can – then store it. Then, if it is wrong there is only one place to look, and it will be obvious since every resulting figure stored will be wrong. In a past life where disk space was expensive I would never have suggested this… but no more. Programming errors are expensive, disk space is cheap. But, I digress…
Now the math on the sale is very simple, totals, tax calcs, average discount, total saving… all this can not be done with simple math since you have the components to do it in one simple table.
To get the total of all the items is easy: just sum the RG elements, no need to go back to the database or try to dig calculated values out of an RG.
Hope this helps…and if other have any better ideas or insights I welcome the conversation, I always enjoying learning and differing points of view.
Hello John, first of all, thank you for such a detailed answer.
From a performance standpoint, it’s difficult to make a general comparison as this will depend on various factors, including the size of the data and the complexity of the queries being performed.
Generally speaking, the first option I suggested of creating a new data type called “Sales Products” with two fields - “Product” and “Discount”, and then including a list of these products in each sale, could be more performant compared to the second option.
This is because when using a separate list of “Sales Products”, the information about each product is stored in a separate record, which makes it easier and faster to access information about each individual product without having to load all the sale information. Additionally, if you need to search or filter sales based on product information, having the products in a separate list can also be more efficient.
In the second option, including a list of products directly in the “Sales” data type may require more database resources as it loads all the sale and product information included in each sale each time it is accessed. However, this will depend on the size of the data and the queries being performed in your application.
In summary, the first option could be more performant, but it’s important to consider other factors in your decision, such as design complexity and the specific needs of your application.
Hi, I hope I did not muddy the waters or misunderstand. What I meant for the data model was.
3 Tables Product (contains all the info about the product) Sale Receipt (contains links to, and information about the sale eg total, taxes, and list field pointing to the sale items) Sale Item (contains the link to the Product, plus other fields unique to this line item).
Each sale item only points to the Product, does not contain all the info about the product. The only specific info copied from Product to this record is the unit cost. All other fields are specific to the sale - such as line item discount and quantity. ect…
Hope I did not misunderstand. I find text hard to work with and a data-model diagram would be much easier… alas text will have to do.
