When you design a database do not mix record (row) structures in a
single table (variant records). That used to be done with ISAM files but
is no longer necessary with the advent of the RDBMS.
Also you should design your database to have "Third Normal Form" which
means that each data item is only stored in one place. Altering that
item in its one location means that it is altered system-wide.
In your case of an owner record linking to a chain of transactions or
detail set you do not need to keep a summary in the owner record, just
make it the entry point to the chain of transactions. You have one
table for the owner rows and another for the transactions.
If you avoid have summaries you simplify the logic of your application.
Insertions and deletions become trivial. You do have to scan the full
detail record set to get totals however.
Another tidy design rule is to partition reference (static) and
transactional (dynamic) data. It can make workflow logic simpler and
more robust.
jose isaias cabrera wrote:
"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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------