Modelling Guidelines

From C3 Integrity
Jump to: navigation, search

Money

As a general rule money should be modelled in both Integrity and the database as a fixed precision numeric type. This results in an accurate and processable representation of the money value. The recommended types as expressed in Integrity and their database types are as follows:

Integrity Type Database Type
Integer int, bigint*
Decimal decimal

*Note that smallint is too small for general use and is not recommended.

In some situations you may only wish to store a money values as text, however this means the stored value will not be usable in calculations. If this is desirable it should be expressed in Integrity and the database as:

Integrity Type Database Type
String varchar

If you are modelling multiple currencies in the same dataset you will also require an additional column to store the currency information along side the amount itself. It is recommended to model this information as a lookup dataset where the lookup dataset contains the currency codes as defined by ISO-4217. This can then be referenced in the validation for the dataset attribute that will store the currency code.

Database Type Storage Limits

Type Lower Limit Upper Limit
int -2,147,483,648 +2,147,483,648
bigint -9,223,372,036,854,775,808 +9,223,372,036,854,775,808
decimal -10^38 + 1 10^38 - 1

Options

The following options are method for modelling money in Integrity. Options colour coded GREEN are recommended, ORANGE are possible, however there are some pitfalls, and RED options should never be used.

Single Integer

The simplest way to store money is in its most reduced form. So if storing AUD the amount is converted to its smallest unit (cents) then stored as that value. Depending on the database this can have limits imposed by the data type used.

For the int types defined in Database Type Storage Limits, this means an 'int' type can store ±21.4748365 million dollars, while a 'bigint' type can represent ±92,233.7204 trillion dollars.

Dual Integer

Money can also be expressed as two parts, the whole unit amount and the fractional amount. This allows for storage of even larger amounts.

For the int types defined in Database Type Storage Limits, this means for two 'int' type columns values can store ±2,147,483,648 dollars with over 2 trillionths of a dollar fractional value, while two 'bigint' type columns can store ±9,223,372,036,854,775,808 (9 quintillion\!) dollars and over 9 quintillionths of a dollar as the fractional amount.

String

Strings should generally not be used to store money as they are inflexible. If there is no desire to work with the values after they are ingested they can be stored as a string however a strict regex should be used to validate the input.

Examples:

Input Sample Regex
No seperators, no decimal precision 1000 ^$?-?(?:\d+)$
Seperators, no decimal precision 1,000 ^$?-?(?:\d{1,3})(?:,\d{3})*$
No seperators, 2 place decimal precision 1000.00 ^$?-?(?:\d+)\.(?:\d{2})$
Seperators, 2 place decimal precision 1,000.00 ^$?-?(?:\d{1,3})(?:,\d{3})*\.(?:\d{2})$

*Note that all examples allow an optional leading currency symbol ($) and an optional negative symbol.

Decimal

Decimals can be used to store money however some databases store the value internally as a floating point number (Teradata) which can lose detail when dealing with higher precision. Unless the internal representation of the decimal type for a given database is known, which many databases don't advertise, it is recommended to avoid using decimal as a database type.

If decimal is used, unless the database informs it otherwise Integrity will assume a precision of 38 and a scale of 2. Note that some databases will silently round extra scale information so a value of 5.567 inserted into a decimal(3,2) will be inserted as 5.57.

Money

Some databases have a specific data type to store money in. They are often analogues of the database's decimal type with smaller fixed precision and scale (SQL Server, PostgreSQL). The money type can also suffers from silent rounding like its decimal counterpart and also suffers from the loss of currency information with the stored value. As with decimal it is no recommended to use money as a database type.

Float

Floats should never be used to store money. Floats are by definition an imprecise storage format storing only the sign, the fraction, and the exponent. This can cause hidden rounding errors to occur.