jose isaias cabrera <[EMAIL PROTECTED]>
wrote:
I have a system that links two or more records to one head record
using a column called ProjID, where ProjID has the unique recNO of the
head
record. Anyway, what I would like to do is something like this...
Imagine,
recNo,ProjID,Invoice,Fund
1,1,,
2,1,10.00,30.00
3,1,20.00,60.00
4,1,100.00,600.00
5,5,,
6,5,8.33,20.00
7,5,1.00,5.00
...
...
recNo is unique. What I would like to do is to keep the head record
(1 and 5, on this instance) updated as the other children values get
updated. Updated means, the Invoice sum of all the child records on
the Invoice
column of the head record, and the same for the Fund column.
This is a very bad design. Relational model doesn't easily incorporate
this notion of "special" records. All records in a table should be
uniform.
I suggest removing these "head" records from the table altogether. You
can always calculate them whenever necessary, like this:
select ProjId, sum(Invoice), sum(Fund)
from tableName
group by ProjId;
You can make this query into a view if you are so inclined.
If, for some reason, you insist on storing these totals in the database
persistently, create a separate table containing just those totals
records. Use triggers to update these totals whenever something changes
in the main table. Again, do not put totals records in the main table.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
