Differences between Total and TotalRate
Problem
If you’ll look in the db schema of IS, in some tables you will find numeric values saved in 2 fields that are similarly named but with the other one postfixed with “Rate”. For this article, we’ll use the CustomerSalesOrder table as example. This table is the main table where online/storefront orders are saved. This table contains the fields Subtotal, SubtotalRate, Total, and TotalRate among others. Most often I noticed that the values for Subtotal and SubtotalRate are always the same. This is also true for the Total and TotalRate. For example:
- Customer orders items that sums up to $300.00.
- Subtotal = 300.00
- SubtotalRate = 300.00
- Adding shipping charge of $25.00.
- Total = 325.00
- TotalRate = 325.00
Looking up the database documentation in connectedbusiness.com:
- Total
- The total balance of the customer in a transaction
- TotalRate
- The total base balance of the customer in a transaction
Well… that didn’t help. The descriptions were too simple for my taste. It’s still not clear or descriptive enough.
Tests
I suspected that the difference of the common field should be in the currency rates. So I did a test by using a customer from France. Logging in with this customer would instruct the website to show EURO currency instead of Dollars. So, let’s say I ordered the same two items as the example above. We’ll also assume that the shipping charge is the same. The results would be:
- Subtotal = 300.00, SubtotalRate = 435.18
- Freight = 25.00, FreightRate = 36.26
- Total = 325, TotalRate = 471.44
Note that the numbers are rounded off to two decimal places.
Conclusions
Based on the results above, we can compute the ratio between the field pairs. The ratio would be 1.4506 for all three pairs. This number is the exchange rate for Euro (with Dollars being the home currency). With these, we can deduce that the fields postfixed with “Rate” are the numerical equivalents of the customer’s currency.
Analyzing this was very important because we were involved with integrations with 3rd party services such as preCharge and Cardinal Commerce and they require us to acquire the total amounts of orders. We were perplexed on what field to use.
April 18th, 2008 at 2:45 pm
This is helpful
April 18th, 2008 at 6:24 pm
I’ve always been amazed why Subtotals and Totals are being created as field of tables. I still remembered my DB lesson about calculated values, that it should not be saved in the database. Now I am wondering why they are on this app.
April 20th, 2008 at 8:29 am
Probably because it would be faster to read a single field to get an order’s total amount than summing up every related records every time an order’s total is requested. Also, in the case of IS, the total amount isn’t just about (quantity x price), there are also other charges that are included in the total amount such as shipping charges and taxes. Keeping this computation in computed fields would mean putting business logic in sql queries. The IS framework uses separate classes/libraries for business logic operations and this could violate their coding standards.
April 24th, 2008 at 3:46 am
I believe some developer’s still do that, even some of my classmates in my DB class. Why do you have to save the value that could be calculated from the values on your tables? In this scenario, I hope the application doesn’t handle the calculation and the updating of the calculated value. Because the calculation you get from doing SQL query is more reliable than the application does. Also, the number of access to the table increases, and the permission of the user to the table must be writable.
Just thinking out loud!
Hope I am making sense…