At the risk of sounding glib, you could store everything as integer grams?

Yes of course. That's why I said I'm sure we could get it to work. But then it has to be converted to kg for actual usage. Either directly in the queries or views.

Having a fixed-point decimal type allows us to not think about once the table is created.

Same issue with money. Most of the time we need to store monetary values with two decimal places (cents).

What kind of application are you developing where there is so little middleware between the database storage layer and the user that converting to/from kilos adds significant additional complexity, but your users aren't technical enough to just deal with having weights in grams?

These are electronic versions of official forms. The user interface and reports should show what is actually being sent or has been sent.

Sure we could deal with it, but there are quite a number of tables due to different forms and messages, and then there's all the reports. Many custom ones thanks to to local officials wanting data from a certain customer in a certain way...

Bureaucratic forms have a tendency to change unpredictably. Today they want weight in kilograms, tomorrow they'll want it in grams, and the day after that in standardized intergalactic weight units. I get what you're saying (and I don't like writing unnecessary mapping boilerplate too), but there should be a middle layer to protect the application from craziness like that.

Thankfully things in our sector is fairly static in that regard, so this is seldom an issue. That sounds quite horrible indeed.

And yes, a fatter middle layer would be nice. Our next generation software will probably have more of that, this code base is over 20 years old at this point...

I realize this response turned out a lot more harsh than I intended. I was in a slight rush so didn't formulate myself well, didn't mean to be abrasive.

> Most of the time we need to store monetary values with two decimal places (cents).

Yeah who cares about fractions of pennies anyway. Just makes things complicated.

Yeah for being so uptight about just about everything, the officials seems quite relaxed about values slipping between the cracks due to lack of decimal places.

Like, total invoice value can only be specified with two decimal digits, typically in foreign currency. Yet we also have to specify per-line value in local currency, also with only two digits. And then the per-line values are used to calculate taxes and whatnot...

The US IRS (tax agency) allows you to round nearly all values to dollars ignoring cents altogether.

You could store it in grams as an integer.