This is called storing derived data and it's a Bad Thing. (tm) It
denormalizes your schema. The real problem is that derived data stored in a
field has a tendency to get out of sync with the data it is derived from.
Therefore you end up writing programs that wipe it and put it back in sync
and all kinds of nastiness. it's just easier to compute the data on the fly,
when you need it. What you really need is a select statement to gather this
data together any time you want to use it.
There are obvious exceptions. (say for speed purposes) but you should always
strive for a normalized schema.
Example:
Tables---
Person
---
personID (PK)
===
name
---
purchase
---
purchaseID (PK)
===
personID (FK)
name
amount
---
Data---
person
---
1 Fred
2 Barney
3 Wilma
4 Betty
purchase
---
1 1 milk 4.50
2 1 eggs 2.00
3 3 diamonds 10,000.00
4 1 gun 25.00
Select person.name,
sum(purchase.amount) as total
from person left join purchase on person.personID = purchase.personID
group by personID
order by name;
Now, by executing this statement you can see how much each person has
purchases without storing these totals in the database.
HTH,
=C=
*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*
-----Original Message-----
From: Beauford [mailto:[EMAIL PROTECTED]]
Sent: Sunday, December 15, 2002 9:08 AM
To: MySQL List
Subject: Automatically totalling columns.
Hi,
I have two tables, one with peoples names in it and one with data associated
to these people. What I need to do is to be able to automatically total
certain items in the data table that correspond with a name in the first
table and then update the name table..
i.e. This is a crude example, but you get the picture. When I add an item
to the data table I want to have it so it automatically gets added to the
correct person in the name table. I also have a third table which references
these two tables. Can this be done from within MySQL.
Names Data
Col. 1 Col 2 Col. 1 Col. 2
Fred $2.24 Milk $1.00
Wilma $4.25 Bread $1.24
Barney $1.00 Eggs $2.19
Betty $3.19 Butter $3.25
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
