--- In firebird-support@yahoogroups.com, "Fabio Gomes" <fabioxgn@...>
wrote:

>
> Hi guys,
>
> I have another question :)
>
> The solution using SUM() and a table with quantitys is working fine

now, but

> i have another doubt.
>
> I have my products table:
>
> product_id
> description
> quantity
>
> This quantity, is the sum of all the records of the product in the other
> table, wich would be the best way to keep it updated?
>
> Should i use a query with a join and SUM() each time i need to see the
> quantity?
>
> Or sould i create a view? or maybe a computed field (i dont have any

idea

> how it works btw)? Or can i even use a trigger?
>
> Which would be the best way to do it?

I would create a view called GetCount or something like that. The
summing mechanism you are using works really well, but it is not very
intuitive when included in a query.

With a view, it looks much more obvious what you are doing, and you
can use the view inside the SP as well, simply join to the view on
(1=1) to get the count for each record.

My personal experience with computed fields has been that they tend to
have performance problems when they involve records other than the
current one. Great for getting the difference between income and
expenses without having to use a specific field for it, but not much
good when you have to join.

I don't think you would want to try to use a trigger. The time saved
by not having to perform your count would be swallowed by the time it
took to update all the records to store the count.

Adam