Thank you, John. This helps a lot.
josé
----- Original Message -----
From: "John Stanton" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Friday, March 16, 2007 12:19 AM
Subject: Re: [sqlite] Re: Adding columns of records and updating the result
to a record
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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
