mongodbcurrencyfinanceaccounting

Correct way to do Ledger system in MongoDB

So we are trying to work out in what format do we store money values in MongoDB we are talking dealing with cents.

For example if someone gets charged 0.04 but we take 30% commission we should get 0.012

But I am sure that we should not be storing data like $0.012 or even in 0.012

because technically you could end up with 0.0125 for example.

I read that you should use something like this. trans_amount | decimal | 10 | Deposit amount

Meaning in MongoDB it would be stored like:

{
trans_amount: NumberInit(00125)
}

However the issue is that if $10 was spend it would be store like

{
trans_amount: NumberInit(01000)
}

Now if we go back to the first above example and try to code it out we would get $1.25 instead of the $0.0125 it should be.

Now I am guessing the correct way would be to have 12 digits?

000000000000

However this works up to when you have a customer that wants to spend Trillion dollars at once (not that that would happen - but you never know)

So before I go and code a transaction database I want to work out the correct way to store cents and dollars in MongoDB for accounting.

According to Mongodb they recommend the following:

{
  price: { display: "9.99", approx: 9.9900000000000002, currency: "USD" },
  fee: { display: "0.25", approx: 0.2499999999999999, currency: "USD" }
}

OR

{ price: 9990, currency: "USD" }

However does approx automatically get done by MongoDB - My guess is no and I would have to work that out myself.

Solution

  • There are couple of ways to do this:

    { price: 9990, currency: "USD" } //See it is not string

    As in the above example, you can store. Here the thing is that number of decimals is fixed at any point of time.

    The scale factor is consistent for a currency; i.e. same scaling factor for a given currency. The scale factor is a constant and known property of the currency; i.e applications can determine the scale factor from the currency.

    In another way, you can use other type:

    {
  price: { display: "9.99", approx: 9.9900000000000002, currency: "USD" },
  fee: { display: "0.25", approx: 0.2499999999999999, currency: "USD" }
}

    Here, approx describes exact decimal value with more decimal. The value you query is represented by display field.

    In one field, encode the exact monetary value as a non-numeric data type; e.g., BinData or a string. In the second field, store a double-precision floating point approximation of the exact value.