Archiving data in a MySQL DB

[Jog On]

Say I have a DB set which holds results and other info about my customers.

This info changes over time and I want to take regular snapshots and compare the changes. An example would be:

Customer_a signs up and I take a snapshot of certain things about their website. This goes into a table

Then each week I want to take the same snapshot but the data will have changed. I'd like to keep each snapshot and be able to do comparisons and generate reports.

Do I need a new table for each weekly snapshot or is there a cleverer/more efficient way to just have the snapshot data in 1 table and archive the historical ones?

I'll be using PHP as the reporting interface.

[Bunk]

Creating new tables for each week sounds like a bad idea to me. I don't see any reason why you couldn't copy the snapshot to an archive table before you update it in the original table. You could add a timestamp to the archived copy so you know the date that copy is from.

[NickFitz]

Is there any particular reason not to just create a new record per customer per week, with a timestamp on it? Storage is dirt cheap these days, after all. At some point in the future you can archive the old data, but it might make more sense to do that on a rolling basis so you've always got the last n months data readily accessible.

[Bunk]

Is there any particular reason not to just create a new record per customer per week, with a timestamp on it? Storage is dirt cheap these days, after all. At some point in the future you can archive the old data, but it might make more sense to do that on a rolling basis so you've always got the last n months data readily accessible.

That was my first thought too. Copying them to a separate archive table would keep the main table cleaner but apart from that I can't see any benefit.

[Zippy]

I'd go with NickFitz's idea and archive every few months.

[Jog On]

Is there any particular reason not to just create a new record per customer per week, with a timestamp on it? Storage is dirt cheap these days, after all. At some point in the future you can archive the old data, but it might make more sense to do that on a rolling basis so you've always got the last n months data readily accessible.

This is what I'm trying to achieve, a new record with a timestamp.

Pardon my ignorance - but where would this record be stored? This isn't covered in "PHP & MySQL for Dummies" (yes I have read it )

[Bunk]

It sounds like what you need to do is:

1. Add a new column to the table, call it timestamp or whatever
2. Change the UPDATE statement you're using at the moment to an INSERT, so instead of overwriting the previous data, it creates a new row. Also, remember to add the timestamp to the INSERT.
3. You'll need to change your SELECT for getting the snapshot out of the DB to make sure it uses the most recent snapshot in the table for that customer.

[Bunk]

This is what I'm trying to achieve, a new record with a timestamp.

Pardon my ignorance - but where would this record be stored? This isn't covered in "PHP & MySQL for Dummies" (yes I have read it )

It would be stored in the same table currently used for the snapshots.

[Jog On]

Thanks. I'd never heard of records until now, can I give an example of what I'm trying to accomplish:

One such snapshot I want to take is how many websites link to a particular site. I'll make a table for this called 'backlinks' and it will have columns:

customer - this will only have 1 entry in the table
Customer URL - this will only have 1 entry as well
date - again only 1 entry
backlinks - this could run into hundreds of thousands of rows


I will want to update this table every week, so would I just amend the new data and add a timestamp to the record? From what I've read a record is just a single row - is this right?

Lets say one week a site has 120 backlinks then a month later it has 400 I'll want to be able to chart this over the 4 weekly updates. Will the records do this for me?

I'd love some reading on this but I don't think I'm googling the right terms

[Bunk]

Ah, slight misunderstanding I think. A record is just another name for a row. There's nothing special about it. We're just talking about creating each row with a timestamp so you can have multiple copies of them in the same table. If each "snapshot" consists of data spread across several rows in several tables it gets a bit trickier, but not impossible.