Let's say an application has a function to change the rate of exchange(for example to calculate the price from EUR to PLN).
It is float. For example 1 EUR = 4.23 PLN. The rate is 4.23.
Suppose the application(Desktop based on Swing) is using Postgres DataBase, and there are many objects there in the database(mapped through hibernate).
The objects have float properties like: priceEUR and pricePLN.
The scenario is: When the user changes the rate of exchange(form 4.23 to for example 3.90) every object's pricePLN property stored in the database should be claculated with new rate and then updated to reflect new ratio between EUR and PLN.
My question is:
Should this be made by a trigger on the database, or the application and hibernate(Java) should do that? What are the pros and cons?
Why don't you calculate the price in the object by the exchange rate * base price when the object is created instead of storing only the result in the object. This would eliminate the need for recalculating the price in a specific currency if the exchange rate changes.
0x2B | ~0x2B
SCJD, SCJP 1.5, SCJP 1.4, SCBCD 1.3, SCWCD 1.4, SCJA, IBM Test 150, 142, 700, 701, Oracle Test 1Z0-51
Joined: Nov 20, 2008
Thanks for the reply, but I'm sorry I don't understand
In the model there are many objects like Service, Merchandise, Order... and among properties like name, date etc. they have
common properties: priceEUR, pricePLN. Those values must be relevant to the accurate rate of exchange... so i think all of them must be updated when the global rate is changed... but i can be wrong, and the model could be not-well designed...
If I understand you correctly the price of an object is calculated by an exchange rate for a currency and the base price for the object.
e.g. A car costs 5000 EUR as a base price. This price doesn't change because this is the price the car is sold in the shop. There must be something like a base price because otherwise an exchange rate would make no sense.
Ok , exchange rate 1 EUR = 4.23 PLN. Hence, the car costs 5000 * 4.23 PLN = 21150 PLN. You don't have to store this price in PLN in your data model for the car. You can calculate the price by the base price of the car (5000 EUR, stored in the data model) which rarely changes * exchange rate (which is not a property of the car and should be stored in a different table, e.g. the exchange rate table which has another object to accesses it)
when you develop the class for your car you can specify the properties like this:
By this the dependent prices are not stored in the model of the car and need not to be recalculated
R van Vliet
Joined: Nov 10, 2007
Can I add that storing currency in floating point formats is usually not a good idea. Have a google on the topic.
EDIT : Ernesto's solution would be the one I'd use
First off, allow me to report that my experience with putting logic in the database (stored procedures, etc.) has not been positive. When taken to extremes, you end up with source code in 2 different languages in 2 different locations, and one of them (the database server) isn't well-represented in most source control systems. I've have project source ZIPfiles delivered to me where only half the program was actually in the ZIPfile (the other half being locked behind someone's firewall on a database server in another geographic location, and where I constantly dad to guess whether/how much of a business function was in the Java code and how much was in whatever vendor database language was in use.
Not that I'm totally against stored procedures, just that there's a time and a place where they're worth the extra grief and a time and a place where they aren't. Also, while certain tasks might perform more efficiently within the database server, there's usually more database clients than servers, so unless it's a really significant need, the extra load on the database server may not be desirable.
Triggers should generally be lightweight functions, and I've no problem with them being used for referential integrity purposes. Using them for business logic is another matter, unless there are multiple clients all depending on the same functionality. In that case, it's acceptable because it concentrates the function in a single maintenance point. But relatively few systems I've worked with have had that requirement.
Anyway, to the problem at hand.
There are 2 ways to do currency conversion, depending on need. One is to convert based on the current exchange rate, the other is to convert based on the exchange rate in effect at the time that the transaction was done. Current exchange rate is more likely what you want when presenting an item for sale, transaction exchange rate is more likely when you actually record a sale (for example).
A trigger could be justified (see above) for recording transaction exchange rates. I don't think I could recommend it for presentation-time conversions, though.
One option when you don't need to record the converted price in the database is to use a View.
Customer surveys are for companies who didn't pay proper attention to begin with.