statistics, run it every time when we need? or update it every time?

searain

For example, an e-commerce site receives order from client, process it through different departments and they work on it by online order information, and order is filled. then this order record has no use for the future process.

The online order record has no financial information in it, the financial information of the client was only in the offline account software on in the payment authorization site. Even for future return or accounting record, it is handled by offline account software record. Or it is handled through the financial record/tool on the third party site, authorizenet etc.

But the online order record may be used for the statistics of the e-commerce site, month or year sales of this e-commerce site.

My question is

1) Do you keep the orders record there for the statistics reasons, so when we need statistics information, the statistics code will run through the orders table and get new statistics.

2) Do you write your statistics codes so when an order is added, the statistics record already updated? so when you need new statistics information you dig out from the online updated statistics table instead of running throught the orders table again to get the new statistics.

Option 1) keep the old orders information in the orders database, for a large site, it might be too many dead orders record there.

Option 2) may have to write too many statistics codes, plus, if you need new kind statistics, you don't have the old orders left.

Or best idea is Option 3)

1) keep the orders record for one year. If we need statistics, we run through the orders table again to get it.
2) make an copy of the whole year old orders to a archived order database, and delete them from alive order database.

When I make a simple e-commerce site, i used to use option 1) and then when I update the site with some statistics marketing tools, i tended to use option 2).

But with the old records piled up there, I am thinking a solution now, it is option 3), that is what i want to do now.

Just check any suggestions to help me solve this problem? any better solutions?

Thanks!

bpat1434

Of course, what you could always do is option 4!!!

You have a table that holds all the currently placed orders (and since they're "useless" after placement, are taking up space).

Each day, (at 12:00:01 am), a script is run (via a CronJob or whatever) which queries the order database and moves all rows from the orders database that were ordered on the previous day, and puts them in a new database table titled the current day.

Example:

ORDERS

+---------------------------------------+
|  ID  |   ORDER NUMBER  |  ORDER DATE  |
+---------------------------------------+
|   1  |   0000000001    |  1-31-2006   |
|   2  |   0000000002    |  1-31-2006   |
+---------------------------------------+

1_31_2006
(starting tomorrow, Feb 1, 2006)

+---------------------------------------+
|  ID  |   ORDER NUMBER  |  ORDER DATE  |
+---------------------------------------+
|   1  |   0000000001    |  1-31-2006   |
|   2  |   0000000002    |  1-31-2006   |
+---------------------------------------+

Then, you can just get info from the tables whose name has the year 2006 or whatever in it.... and you can create daily statistics....

Weedpacket

One thing you should hesitate before doing is "deleting old records". Same as you're supposed to keep your receipts because if you're going to claim a refund on a faulty item, you need proof of purchase (they're also useful when you're trying to plan your budget).

And since you're keeping the old records (as bpat1434 suggests, separate from your active data for performance reasons), there's no need to precalculate statistics: you have all the information you need to calculate any statistics you want to any level of detail. Further, you won't save any time by precalculation (you're going to have to take the time to calculate the anyway, and in fact you'll lose time through calculating statistics that no-one will ever see before they get recalculated again.

I remember a few years back the Google-friendly buzzword for archiving data to make aggregate analyses easier was "data warehousing".