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...
Code:
-- 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:
Code:
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
