CREATE TABLE journal_entries (
 localID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 ccpGeneration SMALLINT UNSIGNED NOT NULL,
 ccpRefID INT UNSIGNED NOT NULL,
 ownerID INT UNSIGNED NOT NULL, /* char OR corp ID */
 ...other...

 UNIQUE ccpID (ccpGeneration, ccpRefID)
)

// setup our variables, this is what we think the current universe is!
$currentGeneration = 1;
$characterID = 2723742347;

// now get the highest refID we have on file for this user!
// and any pedants who want to gripe about not using bound parameters can bite me,
// this is pseudo-code only!
$latestReferenceID = LocalDatabaseRunSQL(
 SELECT MAX(ccpRefID)
 FROM journal_entries
 WHERE ownerID = $characterID
 AND ccpGeneration = $currentGeneration
);

// now hit the API for this character's journal page, we're assuming
// here that there are no errors... really you should always be checking
// for errors :-)
$newData = API.GetJournalForCharacter( $characterID );

// iterate over each row, find the highest ID in this dump
$newMaxRefID = 0;
for each $row in $newData {
 if ( $row->refID > $newMaxRefID )
 $newMaxRefID = $row->refID;
}

// now, if the highest ID we found is below the highest ID we've already
// got, then we can safely assume that the generation has changed. this is
// because we should never get back less data than we already have!
if ( $newMaxRefID < $latestReferenceID ) {
 // ...
} else {
 // just store the row in the database, everything's cool
}

Originally by: Ix Forres

---

Would a more logical approach be to just add the magic number onto the RefID at the API end of things, thus keeping the RefID static?

---