Board index » off-topic » SQL or Table Update?

SQL or Table Update?


2003-08-24 12:54:13 PM
off-topic10
I have a invoice table that has related products sold in a child table. Is
it better to store the total of all products sold in the parent table as a
field and update this field on the post of the child table. or is it better
to SQL the child table and display the SQL results as totals on the invoice
form? Any opinions and suggestions are appreciated.
Thanks In Advance
 
 

Re:SQL or Table Update?

Hi Jerry,
"Jerry Gibbs" < XXXX@XXXXX.COM >wrote in message
Quote
I have a invoice table that has related products sold in a child table. Is
it better to store the total of all products sold in the parent table as a
field and update this field on the post of the child table. or is it
better
to SQL the child table and display the SQL results as totals on the
invoice
form? Any opinions and suggestions are appreciated.

Thanks In Advance


I have used both methods in my time and depends upon exactly what I was
doing.
So, just a couple of questions to ask yourself:
Processing Time:
At the time of saving a particular child record do you also save the parent
record. If not, then adding a total at this level will just add that extra
bit of time as well as diskspace. This might make a difference across a slow
LAN.
At the time of generating the invoice do you already access all child
records that would require totalling. If not then to generate a total on the
fly would once again add overhead (probably more than above).
Impact on Application:
If you are using something like Crystal Reports to produce the invoice then
generating a total at reporting time would be less impact on the main
application as it becomes a function of the report. This of course comes at
the cost of time.
HTH
Leslie.
 

Re:SQL or Table Update?

Hi Leslie,
Thank you for the fast reply. I failed to mention the complete situation.
The invoice file also has other related tables. These related records will
also need to be totaled for other calculations within the application.
Concerns are data normalization, ease of future application updates, safety
of numbers, and performance. I have been reading about database design and
it appears data normalization is the correct practice and I really wanted to
get a feel for what others are doing.
Is it better to take a one time hit when the child tables are posted, or is
it better to take several hits when the totals are needed later in the
application. When the database gets very large and totals are summarized
when needed, how will this impact the performance of the application. It is
also possible that totaling in the parent record could get messed up if
there is a LAN failure or power outage. So with this in mind, which is
better best practice, safety or speed?
Thanks
Jerry
"Leslie Milburn" < XXXX@XXXXX.COM >wrote in message
Quote
Hi Jerry,

"Jerry Gibbs" < XXXX@XXXXX.COM >wrote in message
news:3f4844f2$ XXXX@XXXXX.COM ...
>I have a invoice table that has related products sold in a child table.
Is
>it better to store the total of all products sold in the parent table as
a
>field and update this field on the post of the child table. or is it
better
>to SQL the child table and display the SQL results as totals on the
invoice
>form? Any opinions and suggestions are appreciated.
>
>Thanks In Advance
>
>

I have used both methods in my time and depends upon exactly what I was
doing.
So, just a couple of questions to ask yourself:

Processing Time:
At the time of saving a particular child record do you also save the
parent
record. If not, then adding a total at this level will just add that extra
bit of time as well as diskspace. This might make a difference across a sl
ow
LAN.

At the time of generating the invoice do you already access all child
records that would require totalling. If not then to generate a total on
the
fly would once again add overhead (probably more than above).

Impact on Application:
If you are using something like Crystal Reports to produce the invoice
then
generating a total at reporting time would be less impact on the main
application as it becomes a function of the report. This of course comes
at
the cost of time.

HTH
Leslie.



 

{smallsort}

Re:SQL or Table Update?

Hi Leslie,
Thank you for the valuable input. This gives me much to think about and some
very good ideas to plan with. Again, thank you for your time and knowledge.
Jerry
"Leslie Milburn" < XXXX@XXXXX.COM >wrote in message
Quote
Hi Jerry,

"Jerry Gibbs" < XXXX@XXXXX.COM >wrote in message
news:3f48f52d$ XXXX@XXXXX.COM ...
>Hi Leslie,
>
>Thank you for the fast reply. I failed to mention the complete
situation.
>The invoice file also has other related tables. These related records
will
>also need to be totaled for other calculations within the application.

Ok, so I guess the questions now become

1. On average how many child records would be totalled.
2. How many fields in the child record would need to be totalled.
3. Are all of the totals stored in the same parent record or across
mulitple
parent records ?
4. Will the totals be used anywhere else, consider future statistical
analysis
of sales here as well.

>Concerns are data normalization, ease of future application updates,
safety
>of numbers, and performance. I have been reading about database design
and
>it appears data normalization is the correct practice and I really
wanted
to
>get a feel for what others are doing.

I am a firm believer in data being located in one place only. However, it
is
also a standard practice to allow some denormalization to improve
performance as long as you do not get carried away. BUT, denormalization
should commence *only* once you have fully normalized the data and *then*
you pull back to get that extra bit of performance at the most critical
times. ie. saving a micro-second is worthless.

>Is it better to take a one time hit when the child tables are posted, or
is
>it better to take several hits when the totals are needed later in the
>application. When the database gets very large and totals are summarized
>when needed, how will this impact the performance of the application. It
is
>also possible that totaling in the parent record could get messed up if
>there is a LAN failure or power outage.

If you are that worried about the application being terminated whilst
updating the totals, I suggest you look into transaction processing.

>So with this in mind, which is better best practice, safety or speed?

The only safety is a good backup procedure. So that only leaves
performance.


So, as always to gut feel we turn :-) If I were you I would probably
generate the totals as part of the invoice (assuming you are using a
decent
report generator such as Crystal Reports). Imagine the following scenario:

Each invoice has twenty listed items that require two types of total. The
overhead for Crystal Reports to generate the totals is almost neglible
compared to the time taken to actually load the data. The more data you
load
as part of the report, the less obvious the totalling becomes.

Of course, you could quickly write a report to prove the theory.
HTH
Leslie.