Money type

[mathemancer]

In the roadmap, we have that we want to support a "money" type. The native PostgreSQL money type will be inappropriate for this, since we can't support more than one currency in that way on the same installation (the currency is set by the locale in which postgres is running). So, we'll need to make a custom money type. Questions we need to answer (or otherwise discharge) before it makes sense to implement are:

1. Should we support more than one currency in the same column? E.g., a record of sales in different currencies might have such a column.
2. Do we want to support specific currencies, or somehow let users add a currency symbol that's meaningful to them?
3. Do we want to support converting between currencies (for example, to sum a column containing different currencies; this would be complicated)?
4. Do we want any special formatting? Does this need to be done in the data layer? For example, the PostgreSQL MONEY type will format 500000.000 to $500,000.00 (when in the default C locale).
   • If we want to support special formatting on the DB, what's our appetite for researching the normal formatting of money for different places in the world?
   • On the other hand, if we only do this formatting in the UI, there may be some library that helps at the service or frontend layers, but the formatting won't be available when querying the DB directly.
5. Assuming we should use fixed-precision for all monetary operations (this would definitely be a good idea for anything where calculations will actually matter), what's our appetite for researching what precision different currencies usually use? (For example, the Euro uses 2 places after the decimal, but the Iraqi Dinar uses 3 decimal places. This will be relevant if we want to support dividing 10 EUR by 3 vs 10 IQD by 3).

[kgodey]

1. Should we support more than one currency in the same column? E.g., a record of sales in different currencies might have such a column.

I don't think we should, at least not for the basic money column. If we need a multi-currency money column, we can implement that at a future date.

2. Do we want to support specific currencies, or somehow let users add a currency symbol that's meaningful to them?

My thinking here is that we should have pre-loaded currencies of some sort to reduce friction for users, but also allow them to specify their own currencies in the Mathesar configuration.

3. Do we want to support converting between currencies (for example, to sum a column containing different currencies; this would be complicated)?

I don't think we need to do this, since we won't have a column with multiple currencies.

4. Do we want any special formatting? Does this need to be done in the data layer? For example, the PostgreSQL MONEY type will format 500000.000 to $500,000.00 (when in the default C locale).
   * If we want to support special formatting on the DB, what's our appetite for researching the normal formatting of money for different places in the world?
   * On the other hand, if we only do this formatting in the UI, there may be some library that helps at the service or frontend layers, but the formatting won't be available when querying the DB directly.

I don't think we need to do this in the data layer, we should just have the currency code and the amount. The UI can do the formatting.

5. Assuming we should use fixed-precision for all monetary operations (this would definitely be a good idea for anything where calculations will actually matter), what's our appetite for researching what precision different currencies usually use? (For example, the Euro uses 2 places after the decimal, but the Iraqi Dinar uses 3 decimal places. This will be relevant if we want to support dividing 10 EUR by 3 vs 10 IQD by 3).

We should certainly use fixed precision. I think we can allow the precision to be configurable per-column, and our pre-loaded currency choices could have defaults set up. I'm assuming there's some library or dataset that includes these and we don't have to do our own research. e.g. https://datahub.io/core/currency-codes

[pavish]

Should we support more than one currency in the same column? E.g., a record of sales in different currencies might have such a column.

I think it might be useful, but this will introduce several complexities. How will we be able to perform functional operations on the column like sum, avg?
We will need to perform currency conversion for that, which I think is way too out of scope for Mathesar as a whole. This will need syncing with currency conversion rates and storing it on our DB. I don't think we will need to do that in the core product. If in case, such a feature is deemed useful in the future, we can create a plugin for that.

Do we want to support specific currencies, or somehow let users add a currency symbol that's meaningful to them?

Yes, allowing users use custom currency symbols will be useful. I do agree with Kriti that we will need to have a set of defaults.

Do we want to support converting between currencies (for example, to sum a column containing different currencies; this would be complicated)?

My first points answers this question. I don't think it is needed.

Do we want any special formatting? Does this need to be done in the data layer?

I don't think we need to do this at the data layer. We can do it on the UI or the service layer. Will have to check for libraries to do this, if a stable library is available for the UI, which is also small in size, we can do it on the UI, or if a better library is available for python, we can do it on the service level.

Assuming we should use fixed-precision for all monetary operations (this would definitely be a good idea for anything where calculations will actually matter), what's our appetite for researching what precision different currencies usually use?

I agree with Kriti's views on this.

[ghislaineguerin]

I agree with multi-currency being too complex for Mathesar. The defaults for currency make sense.