This may be a fairly straight forward one. I'm playing around with a little app I'm trying to create. I've got a form where users submit their "buy_price" and "sell_price". I am then trying to work out if they have made a loss or a profit. Would it be better to have a column within the database that works out the "margin" i.e "margin coulmn = sell_price-buy_price" (and how would I do this). Or would it be better to do this within php.
Thank you in advance.
Dan
Edit: I use a database because I store the data and then they can view their purchase history. I then want to make a section such as "best trades" for ones with highest margin etc.
I absolutely would not store calculated values in the database that are this simple - maintenance would likely outweigh any marginal gains in efficiency.
Even without storing the value, you still have an option on whether to calculate the value upon retrieval (with MySQL) or with PHP when rendering.
SELECT buy_price, sell_price, (buy_price - sell_price) AS margin FROM x
SQL aside, I'd still go with calculating it upon render with PHP.
The definition of "better" very much depends on your application. It often refers to which operation is faster, which would require you to run a benchmark (but I'm guessing it's PHP). However, speed is probably not an issue for you, so "better" becomes simply whichever is easier for you to do. This is almost definitely PHP.
It is better to do this calculation in the database and to embed the value in a view:
create view vw_table as
select t.*, (buy_price - sell_price) AS margin
from table t;
Why is using a view (or in other databases a computed column) better?
First, is ensures that the same definition and name is going to be used wherever need "margin". Second, using the view helps to separate the application layer from the database layer. So, you can change the name of a table or column without having to rewrite the application -- you just change the view. Third, in many environments, the margin can be calculated in parallel on a large volume of data, rather than having to be calculated sequentially in the application. (On the other hand, a slightly larger amount of data is being returned to the client.)