Replace decimal(12,4) with decimal(9,3) and decimal(13,3)

[apiuser]

Feature request from colinmollenhour, posted on GitHub Aug 19, 2014

MySQL decimal storage uses a minimum of 4 byes and can store 9 digits in these 4 bytes. Going up to 6 bytes you can store 13 digits.

In most cases decimal(9,3) is sufficient and it would save 2 bytes per row/column. The maximum value would be 999,999.999 (1 million) instead of 99,999,999.9999 (100 million). Unless you are selling original artwork in Iranian Rial this should be sufficient. Also according to the ISO list of currencies there are no currencies which use 4 decimal places (but plenty that use 3, so 2 is not acceptable).

For aggregation tables the better storage type would be decimal (13,3) since still 4 decimals are not needed and 13 takes the same storage space as 12. So for the same storage you can store numbers 100 times larger.

It may seem like a minor optimization but in Magento 1 my sales_ tables currently have over 500 decimal(12,4) columns (some are third-party). 2 bytes 500 columns * 1 million rows is almost 1GB of storage, not to mention the indexed columns. Not huge, but quite significant.

While you're at it, the report_event_types.event_type_id and report_event.event_type_id and report_event.subtype should be tinyint instead of smallint. Maybe these were removed or already changed in M2.. There are probably other columns that could be optimized as well. I think there is even a strong case to be made for changing store_id to "tinyint unsigned" throughout since operating over 256 stores is an edge case.

I realize the DBMS abstraction may be an impediment to this change but considering only MySQL is actually supported I think that is far less important than a database that isn't wasteful of resources.

Lastly, for anyone who is an edge case and decimal(9,3) is not sufficient it should be quite easy to install Magento, dump the database, use sed or a text editor to do a simple replacement and then re-import.

[apiuser]

Comment from DavidBruchmann, posted on GitHub Sep 15, 2014

@Colin, you always find arguments for avoiding changes concerning the digits and keeping your random opinion. You even excluded already a whole branch with the words they don't use magento anyway. I think like this you give a good reason for forking the project or just using something else.

On Tue, Sep 9, 2014 at 9:09 PM, Colin Mollenhour notifications@github.com wrote:

@acharrex https://github.com/acharrex I see your point, perhaps, but your example is flawed because 115.880 * 1.08 still rounds to 125.150.

— Reply to this email directly or view it on GitHub https://github.com/magento/magento2/issues/636#issuecomment-54974153.

[apiuser]

Comment from choukalos, posted on GitHub Sep 15, 2014

@DavidBruchmann , @barbazul , @FiveDigital, @tzyganu , @colinmollenhour from my perspective it sounds like there are several inter-related threads:

1. How many digits of precision to store in DB
2. Currency Conversion calculation/precision
3. B2C/B2B/Telecom pricing needs ( i.e. service for x digits of precision * qty and not break rounding )
4. Tax calculation (aka rounding issues)

For item 1 please note we need to store more details for orders to properly calculate sales/shipping tax. That work might impact how many digits we have to store ( issues #444 ). As @ilol stated we'll look into this but it's tied up with the other item's which compound the matter.

For item 2 - currency conversion. Isn't that just saying we should store a # of significant digits for currency conversation ratios (i.e. just 1 table; for the most part)? This seems pretty reasonable to adjust

For item 3 B2C - we really only support 2 digits of precision; so per ISO 4217 we're really not supporting those currencies using 3 or 4 digits of precision (i.e. COU, LYD, XBT [ I thought there was a bitcoin craze going on... heck there's 8 digits there ] ; of which majority of currencies are 2 digits of precision ). To support this we'd need to enable a configuration for merchants to change from say 2 digits to 8 digits (bit coin; 4 if we stick to non-digital currencies) of precision and show that precision on all prices in Magento. This would also impact storage and tax calculations. This seems pretty risk for what is a handful of countries with a small share of the e-commerce market. Which e-commerce sites are located/used in Tunisia, Oman, Libya, Colombia...? If one were to customize this behavior is it do able given the price template work and consolidated tax logic? What else would need to be done to help support community efforts here?

For item 3 B2B support - that tends to look more like digits of precision for a product * some amount/rounded to the currency precision as a line total. This should be easier to customize with the recent price template refactoring and consolidated tax calculation logic work. At present our focus is B2C.

For item 4 - @FiveDigital is totally right; I wouldn't feel comfortable making a change for item 3 until we've done more work around automated testing ( considerably more work ).

[apiuser]

Comment from barbazul, posted on GitHub Oct 16, 2014

Regarding item 3.

I don't know about many of those countries but I have worked in 2 Magento websites for Colombia. I am pretty sure I wasn't lucky enough to stumble upon the only 2 ecommerce sites in that country (I am not colombian and I have never been to Colombia).

Also, they have an annual ecommerce day conference that.claims to be the largest in America Latina http://www.ecommerceday.co/2014/

So i'd dare say Colombia has a significant ecommerce activity.

More to the precision matter: I recently started working in a Magento store for Chile and was surprised to find out that they have no decimals in their currency. Check out this website: http://www.falabella.com/falabella-cl

So again, I will have to hack my way around all those hardcoded number_format(2) that could so easily be configurable... El 15/09/2014 13:14, "Charles Choukalos" notifications@github.com escribió:

@DavidBruchmann https://github.com/DavidBruchmann , @barbazul https://github.com/barbazul , @FiveDigital https://github.com/FiveDigital, @tzyganu https://github.com/tzyganu , @colinmollenhour https://github.com/colinmollenhour from my perspective it sounds like there are several inter-related threads:

1. How many digits of precision to store in DB
2. Currency Conversion calculation/precision
3. B2C/B2B/Telecom pricing needs ( i.e. service for x digits of precision
   • qty and not break rounding )
4. Tax calculation (aka rounding issues)

For item 1 please note we need to store more details for orders to properly calculate sales/shipping tax. That work might impact how many digits we have to store ( issues #444 https://github.com/magento/magento2/issues/444 ). As @ilol https://github.com/ilol stated we'll look into this but it's tied up with the other item's which compound the matter.

For item 2 - currency conversion. Isn't that just saying we should store a

of significant digits for currency conversation ratios (i.e. just 1

table; for the most part)? This seems pretty reasonable to adjust

For item 3 B2C - we really only support 2 digits of precision; so per ISO 4217 we're really not supporting those currencies using 3 or 4 digits of precision (i.e. COU, LYD, XBT [ I thought there was a bitcoin craze going on... heck there's 8 digits there ] ; of which majority of currencies are 2 digits of precision ). To support this we'd need to enable a configuration for merchants to change from say 2 digits to 8 digits (bit coin; 4 if we stick to non-digital currencies) of precision and show that precision on all prices in Magento. This would also impact storage and tax calculations. This seems pretty risk for what is a handful of countries with a small share of the e-commerce market. Which e-commerce sites are located/used in Tunisia, Oman, Libya, Colombia...? If one were to customize this behavior is it do able given the price template work and consolidated tax logic? What else would need to be done to help support community efforts here?

For item 3 B2B support - that tends to look more like digits of precision for a product * some amount/rounded to the currency precision as a line total. This should be easier to customize with the recent price template refactoring and consolidated tax calculation logic work. At present our focus is B2C.

For item 4 - @FiveDigital https://github.com/FiveDigital is totally right; I wouldn't feel comfortable making a change for item 3 until we've done more work around automated testing ( considerably more work ).

— Reply to this email directly or view it on GitHub https://github.com/magento/magento2/issues/636#issuecomment-55614471.

[apiuser]

Comment from pljspahn, posted on GitHub Dec 05, 2014

I'm putting in my $0.02000 here because I am currently faced with implementing 5 digit precision in a Magento 1.9 build.

For those who believe there are no good use cases for this, they are ignoring real-world businesses that expect this sort of precision. "Our old system allowed us to do this!"

As previously pointed out, consider the site that sells things in large quantity where each unit has a small price. Nails, screws, etc were mentioned, but also think about pricing per linear inch / square inch / etc.

What I need currently amounts to:

• Simple product with 5 digits of precision on the price (ie. 0.02125)
• In cart, quantity is calculated as a simple W*H formula, units in square inches.
• An item that is 36" x 30" should cost $22.95 ( 36 30 0.02125 )
• With only three digits of precision the price instead is $22.68, with four it's now $23.04
• Consider the merchant has high volume where this critically impacts their business model
• Also consider that there may be 8-10 (or more!) items in the cart that all use this pricing formula

TL;DR - Merchants need to be able to decide how many digits of precision they will use. In the case I provided, we need 5 (maybe even 6 for some products). I agree that merchants who only require 2 digits shouldn't have to bear the brunt of the extra precision.

Maybe the best solution is to provide an option during installation where pricing precision can be chosen by the admin/developer/merchant.

[apiuser]

Comment from colinmollenhour, posted on GitHub Dec 05, 2014

@pljspahn Thanks for the input, that is a good scenario to consider. Some thoughts on the matter:

Magento does support decimal quantities, so using larger units and decimal quantities could be a solution that would work even with current limitations. That is if units are currently square inches, store prices in price per square feet or per 100 square inches. This might make sense anyway since as a customer $3.06/sqft is arguably more readable than $0.02125/sqin. Then if they order 36" x 30" that would simply be 3' x 2.5' = 7.5 units so the example problem would be supported just fine by existing limitations. Template updates can always be used to convert to smaller units if necessary. Just a thought.

As I said before, even if the merchant absolutely must store the product price using higher precision, the order totals and similar do not need higher precision since they will be rounded to exchangeable monetary amounts and they account for probably 90% of the decimal columns in the database.

I agree that having the merchant choose precision configuration upon installation would be a good solution. Another possibility that may be more EAV friendly would be to take the middle-ground and support two decimal types: low precision and high precision that are either fixed or configurable. Low precision would be for monetary amounts and high for things like tiny unit prices or currency exchange rates

[apiuser]

Comment from DavidBruchmann, posted on GitHub Dec 06, 2014

Configuration of high or low precision had to be done on base of the currency perhaps. So if several currencies are used, should the option exist for every currency? The configuration of precision might still differ from configuration of display-length. So I could configure precision of 7 digits but display only 5, 3 or even 2 or 0. In general the question is arising then why I can configure in-precise calculation but that might be a feature related with rounding.

On Sat, Dec 6, 2014 at 12:26 AM, Colin Mollenhour notifications@github.com wrote:

@pljspahn https://github.com/pljspahn Thanks for the input, that is a good scenario to consider. Some thoughts on the matter:

Magento does support decimal quantities, so using larger units and decimal quantities could be a solution that would work even with current limitations. That is if units are currently square inches, store prices in price per square feet or per 100 square inches. This might make sense anyway since as a customer $3.06/sqft is arguably more readable than $0.02125/sqin. Then if they order 36" x 30" that would simply be 3' x 2.5' = 7.5 units so the example problem would be supported just fine by existing limitations. Template updates can always be used to convert to smaller units if necessary. Just a thought.

As I said before, even if the merchant absolutely must store the product price using higher precision, the order totals and similar do not need higher precision since they will be rounded to exchangeable monetary amounts and they account for probably 90% of the decimal columns in the database.

I agree that having the merchant choose precision configuration upon installation would be a good solution. Another possibility that may be more EAV friendly would be to take the middle-ground and support two decimal types: low precision and high precision that are either fixed or configurable. Low precision would be for monetary amounts and high for things like tiny unit prices or currency exchange rates

— Reply to this email directly or view it on GitHub https://github.com/magento/magento2/issues/636#issuecomment-65823820.

[apiuser]

Comment from choukalos, posted on GitHub Dec 09, 2014

@pljspahn @colinmollenhour @DavidBruchmann Currently I'm tracking two feature requests (in our backlog; TBD) - Bulk Pricing, and controlling degrees of pricing precision. For the latter all the permutations concern me - I'd think it'd be safer to just configure pricing/rounding for 0 or 2 or 4 digits (maybe add 3 for the middle east countries depending on testing cost). @barbazul did you create automated tests - could you share those?

[apiuser]

Comment from barbazul, posted on GitHub Dec 09, 2014

I have code for magento 1.7 with tests for TAF. Is that of any use?

I could work in migrating it to m2 but it could take some time El 09/12/2014 12:57, "Chuck Choukalos" notifications@github.com escribió:

@pljspahn https://github.com/pljspahn @colinmollenhour https://github.com/colinmollenhour @DavidBruchmann https://github.com/DavidBruchmann Currently I'm tracking two feature requests (in our backlog; TBD) - Bulk Pricing, and controlling degrees of pricing precision. For the latter all the permutations concern me - I'd think it'd be safer to just configure pricing/rounding for 0 or 2 or 4 digits (maybe add 3 for the middle east countries depending on testing cost). @barbazul https://github.com/barbazul did you create automated tests - could you share those?

— Reply to this email directly or view it on GitHub https://github.com/magento/magento2/issues/636#issuecomment-66305053.

[apiuser]

Comment from pljspahn, posted on GitHub Dec 10, 2014

@colinmollenhour - Ultimately I will likely have to resort to decimal quantities and a different "price/per" strategy. As a developer, I don't really care one way or the other how it works. My client, however, does care as that means their existing pricing model must change. Keep in mind that while this specific client is a former engineer and will understand the "why", most clients will not and may refuse to consider Magento based on this limitation.

Also, the fact that prices will be rounded to the currency's precision in the cart is beside the point. There are a lot of potential calculations that will happen on that price before it ends up in the cart. Just think about a customer purchasing the product I previously described (36" x 30" dimensional product) in conjunction with a handful of other related items (all with similar pricing structures; think bundled product with all dimensional options) and then purchasing, say 30 of those "bundles" at once with a %off coupon code. You get the picture ...

I guess a better example would be to think about how you might structure a pricing model for a bundled/grouped/configurable product where a doll house was being sold (not my situation but I see the similarities). Let's say the master/parent product is "Doll House". Options might include number of floors, number of rooms, carpet/wallpaper options per room, electrical wiring, outer siding/shingles/roof, etc. If the guy that is selling this kind of doll house bases all of his margins on price per sq.inch (as all of his existing software infrastructure does) then changing his pricing structure (again, along with all the existing software) to suit Magento makes a lot less sense than changing Magento to suit his needs. Remember, for many store owners, their Magento site might only represent a single sales channel out of a handful or more. Changing their brick and mortar point of sale that runs on HP-UX and was last updated 25 years ago is likely not a viable option.

@choukalos @DavidBruchmann I still think you need to consider that extra precision is needed regardless of the currency being used. I may be using USD as my currency, but I still require 4 (or in my case, 5) decimal places of precision at the product level.

[apiuser]

Comment from DavidBruchmann, posted on GitHub Dec 10, 2014

@pljspahn I don't know what you mean with extra precision. Nevertheless, after the changes are now accepted in general by the developers I think it might be worth it to do it correct instead of moving the same problem again a bit more to the right from the price-point. Having read the idea of @chuck to just configure pricing/rounding for 0 or 2 or 4 digits with the argument of safety, I never knew what to say - I'm just in loss of words. Assumed that's a big task to implement new calculations, it won't change much if it's done correct I think, neither concerning the work nor concerning safety.

Apart from that I know it's not always easy to adapt foreign ideas and to work on a project where these ideas have to be implemented. Therefore to the developers many thanks for the discussions and the time spent on programming.

On Wed, Dec 10, 2014 at 9:27 AM, pljspahn notifications@github.com wrote:

@colinmollenhour https://github.com/colinmollenhour - Ultimately I will likely have to resort to decimal quantities and a different "price/per" strategy. As a developer, I don't really care one way or the other how it works. My client, however, does care as that means their existing pricing model must change. Keep in mind that while this specific client is a former engineer and will understand the "why", most clients will not and may refuse to consider Magento based on this limitation.

Also, the fact that prices will be rounded to the currency's precision in the cart is beside the point. There are a lot of potential calculations that will happen on that price before it ends up in the cart. Just think about a customer purchasing the product I previously described (36" x 30" dimensional product) in conjunction with a handful of other related items (all with similar pricing structures; think bundled product with all dimensional options) and then purchasing, say 30 of those "bundles" at once with a %off coupon code. You get the picture ...

I guess a better example would be to think about how you might structure a pricing model for a bundled/grouped/configurable product where a doll house was being sold (not my situation but I see the similarities). Let's say the master/parent product is "Doll House". Options might include number of floors, number of rooms, carpet/wallpaper options per room, electrical wiring, outer siding/shingles/roof, etc. If the guy that is selling this kind of doll house bases all of his margins on price per sq.inch (as all of his existing software infrastructure does) then changing his pricing structure (again, along with all the existing software) to suit Magento makes a lot less sense than changing Magento to suit his needs. Remember, for many store owners, their Magento site might only represent a single sales channel out of a handful or more. Changing their brick and mortar point of sale that runs on HP-UX and was last updated 25 years ago is likely not a viable option.

@choukalos https://github.com/choukalos @DavidBruchmann https://github.com/DavidBruchmann I still think you need to consider that extra precision is needed regardless of the currency being used. I may be using USD as my currency, but I still require 4 (or in my case, 5) decimal places of precision at the product level.

— Reply to this email directly or view it on GitHub https://github.com/magento/magento2/issues/636#issuecomment-66395215.