Archiving data in a MySQL DB

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

[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

[Zippy]

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

See ya

[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

[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

[Zippy]

Well done NickFitz

[Jog On]

Thanks Nick - pure legend

[NickFitz]

legend

Which end - hip or ankle?

[Ardesco]

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

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

[NickFitz]

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

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

+1