2

Am refering to moneyphp/money library which I plan to use it in one of our projects for dealing with currencies. The Documentation is great except it does not touch how to persist in the database. It speaks about encoding money object to JSON but storing strings makes it difficult to make queries as simple as comparing the amount or currencies.

Going through issues, I have found few discussion about Doctrine and it is so specific to doctrine that I could not benefit anything.

Asking my question, in concise words: How do I store money object without loosing details? I'm interested in Postgresql but any relational database is OK.

Note: I have read many different way to persist monetary in database and they differ in how they do. Am interested in specific way that works with this specific library.

Stefano Mtangoo
  • 6,017
  • 6
  • 47
  • 93
  • Is there anything that speaks against storing both fields separately? Seems the most natural way to do this. – Yoshi Feb 07 '18 at 10:23
  • no. I don't have that limitation. As long as storing/retrieving will be loseless then it is fine. The only limits are (1) it should be easy to run queries (2) saving and retrieving data should be loseless. – Stefano Mtangoo Feb 07 '18 at 10:27
  • For the benefit of those who aren't familiar with the library, what exact attributes do you need to store? – Álvaro González Feb 07 '18 at 10:28
  • 1
    If *splitting* the attributes is ok, then I think storing lossless depends entirely on the database system. But checking: http://moneyphp.org/en/latest/concept.html#integer-limit implies that using anything other than a string type should be discouraged. Meaning doing calculations in the database could be problematic. – Yoshi Feb 07 '18 at 10:31
  • Am not yet well versed in it so am not sure if there is extra information that the library need to store to be able to reconstruct object correctly. But reading the docs it seems amount and currency are enough. The class is here https://github.com/moneyphp/money/blob/master/src/Money.php and docs http://moneyphp.org/en/latest/getting-started.html – Stefano Mtangoo Feb 07 '18 at 10:32
  • @Yoshi, so what are you suggesting as they also discourage using floating point numbers? – Stefano Mtangoo Feb 07 '18 at 10:34
  • 1
    I'd suggest two string fields (with appropriate length) and not doing calculations inside the database. – Yoshi Feb 07 '18 at 10:35
  • I see. So what length are you suggesting? I know that I should be doing the research for that but wanted to hear your opinion – Stefano Mtangoo Feb 07 '18 at 10:51
  • added an issue: https://github.com/moneyphp/money/issues/454 – Stefano Mtangoo Feb 07 '18 at 11:13

1 Answers1

2

After tiresome reading of many different threads, posts et al, I have come to the conclusion that only two way are acceptable (see great comments in this question):

  1. Convert all money to smallest unit (eg. Cents) each time you save to the database and reconvert when retrieving it.

  2. Use DECIMAL/NUMERIC data type with needed accuracy (Many recommend NUMERIC(15,4) when doing normal operations or NUMERIC(15, 6) if you do currency exchange operations)

Of course whatever the choice, it must be coupled with Currency column.

I have ended up with the first method because of this library (which is not the original when I asked the question), which makes that super easy. Here are explanations from author after I created an issue:

I can only tell you that I personally store my monies in the database as an integer representing the amount in minor units (cents), and optionally a CHAR(3) for the currency if the application uses multiple currencies (otherwise the currency is hardcoded in the app).

You can get the amount in minor units this way, as an integer:

$money->getMinorAmount()->toInt();

And you can retrieve a Money from an integer stored in the database using:

Money::ofMinor($integerAmount, 'USD');

Stefano Mtangoo
  • 6,017
  • 6
  • 47
  • 93
  • Note, your example code is for [brick/money](https://github.com/brick/money), not `moneyphp/money` ;) – BenMorel Feb 08 '18 at 16:59
  • I have given up the library for two reasons (1) Harsh response to the issue I opened about the same compared to friendly response to the other (linked). (2) The brick library supports automatic conversion to and from minimum conversion unit thereby simplifying the matter. I will bold that part! – Stefano Mtangoo Feb 09 '18 at 12:18