Reply to: Archiving data in a MySQL DB

[NickFitz]

I always found xampp quite useful for a windows dev environment as well

http://www.apachefriends.org/en/xampp-windows.html

+1

[Ardesco]

I always found xampp quite useful for a windows dev environment as well

http://www.apachefriends.org/en/xampp-windows.html

[NickFitz]

legend

Which end - hip or ankle?

[Jog On]

Thanks Nick - pure legend

[Zippy]

Well done NickFitz

[NickFitz]

OK, this is quick and dirty, but it should point you in the right direction - otherwise, I assume somebody will jump on it and correct me

Here's a cut-down version of the database schema creation stuff: use your tool of choice to make something like this...

-- Server version	5.1.30-log
--
-- Create schema JogOn
--

CREATE DATABASE IF NOT EXISTS JogOn;
USE JogOn;

--
-- Definition of table `JogOn`.`Backlink`
--

CREATE TABLE  `JogOn`.`Backlink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

-- Definition of table `JogOn`.`Customer`
--

CREATE TABLE  `JogOn`.`Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

--
-- Definition of table `JogOn`.`Snapshot`
--

CREATE TABLE  `JogOn`.`Snapshot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `customerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

--
-- Definition of table `JogOn`.`SnapshotToBacklink`
--

CREATE TABLE  `JogOn`.`SnapshotToBacklink` (
  `snapshotId` int(11) NOT NULL,
  `backlinkId` int(11) NOT NULL
)

Once populated with data, this will allow you to execute queries such as:

SELECT Snapshot.id AS SnapshotId, Snapshot.timestamp AS snapshotTime, 
Customer.name AS punter, 
Customer.url AS thatWhichWasLinkedTo,
Backlink.url AS theOneThatLinked
FROM Customer, Snapshot, Backlink, SnapshotToBacklink
WHERE Backlink.id = SnapshotToBacklink.backlinkId 
AND Snapshot.id = SnapshotToBacklink.snapshotId
AND Snapshot.id = 1
AND Customer.id = 2
ORDER BY Backlink.url ASC

which will give you all the backlinks, in ascending alphabetical order, for a particular customer (Customer.id) from a particular snapshot (Snapshot.id).

This structure means that if the same URL backlinks to two or more of your punters, you only have to store it once. However, this makes the creation of new stuff slightly more complex. You need to insert a new snapshot record (or row) in the Snapshot table when creating a new snapshot, then insert the new backlink row (or record) into the Backlink table unless it's already there, then link the new (or existing) backlink to the snapshot by inserting a new row (AKA record) into the "SnapshotToBacklink" table.

You'll want PHP functions like mysql_insert_id() to help tie the stages of the transaction together. If you don't need to worry about multiple occurrences of the same backlinking-url then it simplifies things, but also limits your options for looking at the data from the other angle later.

Anyhoo... this is all a bit of a mish-mash (after all, I've just got back from the pub and should have gone to bed twenty minutes ago) but it should give you some interesting starting points for your own explorations. Remember, the mySQL documentation and PHP documentation are your friends

Oh BTW, you probably already know that you shouldn't play with this stuff on an important server. Just to be safe, I'll say it again:

Don't play with stuff on an important server!

To play on an unimportant server, WAMP is also your friend: an Apache/PHP/mySQL server to play with on Windows (MAMP for the Mac, LAMP for Linux) without having to worry about damaging anything important. Play with this stuff there rather than mucking about with an important server. (Just remember that if you get something important together on it, you should move it to a proper server ASAP.)

HTH; I'll probably add more as and when I both have time and remember

[Jog On]

I'm actually a bit busy at ClientOrg today (for a change) but I'll get something written up later for you

Thanks

[Zippy]

Given that JogOn appears to be a sheep - has anyone done a 'tup'les gag yet?

See ya

[NickFitz]

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

I'm actually a bit busy at ClientOrg today (for a change) but I'll get something written up later for you

[Jog On]

I think what I might do is this:

have 1 table for backlinks
Every week I'll update it with any new ones and time stamp them
have a separate table for website info with a column for 'number of backlinks'


Then I can do queries to show all new backlinks from certain dates and make comparisons.

Does this sound like the way to do it?

[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.

[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]

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.

[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.

[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 )