Re: financial database / data management
Federico valori wrote:
> Thank for your reply. I am still not sure if i can
> evaluate all the data i need on-the-fly using the
> sql logic (annualised volatility, standard
> deviation etc.. etc...). even if i manage to do
> so, will it make it slower, if everytime mysql has
> to evaluate these functions? remember this data is
> for search and reporting facility.
I'm quite sure that having the database calculate the aggregates is both faster and less error prone than doing this from your php script. This is all the more true when youre dealing with a lot of data for wich the aggregates need to be calculated. Think of it, say you have like tens of thousends of rows of raw data for wich you want to calculate one standard deviation. Do you really want to pull all those records to your php engine, and calculate the result there when you can do it on the database, and transfer just the one result row?
> rememeber that i need all this data for reporting
> and searchng facilities.
>
> the php functions way seems more reasonable, and I
Why? I'm curious.
> could trigger it in such a way that everytime the
> information about a product is updated, i.e. new
> monthly data is inserted, then all the other
> fields can be re-evaluated and updated.
>
Well, you can do that on the database as well. If you are quite sure you want calculate aggregates on beforehand and store the results rather than calculate them each and every time, you can also have a sql script scheduled that does this. I really don't see the added value of having a php script doing this, or it would be because you feel more sure programming php than SQL. So far, the calculations don't seem that exotic, or am I missing something?
> which way would you say is best?
There would really have to be a very very pressing reason for me to do this in php rather than directly in a sql script.
> in both cases i still need access to these
> financial functions.
I guess I would invest my time in investigating exactly what functions I would need. So far, I guess that standard PHP and standard MySQL are at a par. Maybe MySQL has a bit of a headstart, as it has stddev - php has not.
By the way, I know it's beta, but MySQL >= 5 supports stored procedures, functions (alas, no aggregates) and views, perfect tools to store complex, data oriented logic. In prior versions you can dynamically link externally written C functions. You can even write your own aggregate functions. If you are seriously planning on going all the way with this, that might very well be the way to go.
Good luck, and keep us all posted on your progress.