"Igor Tandetnik" wrote,
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.
First of all, thank you for your help. I am new to DBs. So, I am learning
by myself, just using old homegrown logic, but I want to learn and I thank
you for your time. Now, how do you suggest a relational system with
records? I could also have another table with parents. The problem is that
I need to have these head records because they guide my tool.
I suggest removing these "head" records from the table altogether. You
I don't get it. What is so bad about it?
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.
Thanks.
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.
Ok, I could do this. However, I am still intrigued why this is so bad. Or
is it bad because the head and the children are together?
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
