>"Bob Hall" <[EMAIL PROTECTED]> wrote:
> > Quorting someone else who's name is no longer present:
> > >Is there any way to make one field in a table calculate another field.
> > >I'm making a DB that calculates commissions. So in the money_made
> > >field would have the # of sales (sales field) times whatever the
> > >commission would be.
> >
> > Yes sir, but don't do it. That's the classic example of something
> > that should not be in a table. Calculated fields belong in queries.
>
>In a lot of cases that's true to prevent storing of redundant data.
>However, the poster's example was about sales and commissions. In the real
>world commission rates within a company can change over time so if the
>poster is trying to calculate the commission in conjunction with an INSERT
>STATEMENT it might be wise to have the commission rate in a variable in the
>associatd application and add the commission as the record is entered. Then
>when the commission rate goes down (or up), a variable can be modified and
>the application will work fine. If the commission rate is already in the
>table (the poster didn't state this, but it could be) then it might make
>sense to calculate the commission instead of saving it in a field.
Sir, it doesn't matter whether the commission rate is stored in the
table or the application. In either case, the application can
generate an SQL statement that calculates the commission. Commission
rates can be changed either in the client application or in the table.
>Then
>again, if the number of rows is large and reports are going to be generated
>based on things like total commissions by sales person by month, it might
>create more overhead than desired.
If the reports are generated monthly or less often, then the overhead
isn't going to matter much, unless you have a huge organization with
thousands of sales people, and the query that calculates the
commission is very complex. I have had to include calculated fields
in tables occasionally, but only when the SQL optimizer was confused
by several layers of subqueries. For obvious reasons, this has never
happened with MySQL.
>
>--
>Steve Werby
>President, Befriend Internet Services LLC
>http://www.befriend.com/
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
