I was just trying pgadmin II and I noticed that my tables using numeric values are displayed as numeric(65535,65531). I'm wondering if this is possible (or is pgadmin II displaying the default values wrong?) - it looks like a crazy value. Also, is there a way to display the numeric precision and scale information in psql?
Since I was using the default mapping for JBoss 3.0.0/PostgreSQL 7.2, I checked the standardjbosscmp-jdbc.xml file in the conf directory of the JBoss server directory and changed the java.lang.BigDecimal mapping from 'numeric' to 'numeric(18,5)'. Dropping the table and restarting JBoss has corrected the problem to a sane value.
However, this little scenario prompted me to reconsider how I was using numeric. I'm wondering what numeric precision & scale people are using for money and quantities.
Currently, my application needs to work with Canadian and American dollars and Dutch guilders but I'd like to make it fully international. My quantities are mostly integer quantities (ie. the physical count of items in inventory) but the way I have inventory items designed allows for different units of measure to be used with each item. This means some inventory items could have a fractional quantity (ie. if they are quantities of weight, area, length or time).
My current thought is:
MONEY
Each tuple with money has a 'fk_currency' field which indicates which currency the money will be in (for that entire tuple).
numeric (18, 5)
This allows almost 10 billion dollars to be stored in a single field which is way beyond anything I need now but seems unlikely to be exceeded
in any tuple with a money field in it. However, I wonder if it is good practise to store everything in terms of dollars or if it is better to use the most
basic unit of currency (ie. cents).
QUANTITY
'fk_uom' (There may be several fk_uom links in the same tuple (ie. fk_quantity_uom, fk_weight_uom, fk_distance_uom)
numeric (18, 5)
No reason at all except that its a big number. How much precision to people generally want for quantity fields?
Any thoughts?
Patrick