Defect #1050

SHOP Pricing does not support "large" amounts

Added by Blatti Yves almost 4 years ago. Updated over 3 years ago.

Status:ClosedStart date:06/17/2015
Priority:HighDue date:
Assignee:-% Done:

100%

Category:SHOP
Target version:4.3.2
Affected version:4.3.0

Description

SHOP Pricing does not support "large" amounts due to low storage definition in databases.
Surface rate, fees, min and max values, totals... ALL have the same datatype : DECIMAL(6,2) which means that 9'999.99 is the max.
This is way too low.

We should be able to store at least millions + some margin (low value currencies or enormous orders).

Note:
Storage size optimization: DECIMAL(precision, scale)
MySQL:
https://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-characteristics.html
packs nine decimal (precision) digits into 4 bytes
+
1 byte for every 2 leftover digits

MSSQL:
https://msdn.microsoft.com/fr-ch/library/ms187746.aspx
precision bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

PostgreSQL:
http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL
two bytes for each group of four decimal digits, plus eight bytes overhead but physically stored without any extra leading or trailing zeroes

----
So DECIMAL(19,2) looks good since it fits on 9Bytes for MySQL and MSSQL (most used easySDI DBMS) and 10Bytes(Maximum)+overhead on PgSQL.

The new datatype should be applied to all fields containing prices, fees, etc, min, max, etc.

History

#1 Updated by Battaglia Marc almost 4 years ago

  • Status changed from New to Affected
  • Assignee set to Battaglia Marc

#2 Updated by Battaglia Marc almost 4 years ago

  • Status changed from Affected to Resolved

#3 Updated by Battaglia Marc almost 4 years ago

  • Assignee changed from Battaglia Marc to Blatti Yves

#4 Updated by Blatti Yves almost 4 years ago

  • Status changed from Resolved to To merge
  • Assignee changed from Blatti Yves to Battaglia Marc
  • % Done changed from 0 to 100

All good to me (MySQL)

#5 Updated by Van Hoecke Hélène almost 4 years ago

  • Status changed from To merge to Closed
  • Assignee deleted (Battaglia Marc)
  • Target version set to 4.3.2

Also available in: Atom PDF