[fowlplay4]

I currently do a hybrid of the above on Zodiac. I cache all item data in the SQL DB as it's needed and use a time-stamp and only update the information when it needs to be.

PHP Code:

public function MUDLoadItems(items) {
  // Determine which item need to be updated and remove 
  // up to date items from the items list as needed.
  temp.clause = SQL.generateInClause("arc", items); 
  temp.query  = "SELECT arc, wname, lastupdate
                   FROM MUDItems
                  WHERE " @ temp.clause @ " AND lastupdate > " @ timevar2;
  temp.data = SQL.executeSQL2("items", temp.query, true);
  for (temp.row: temp.data.rows) {
    temp.updated = int(temp.row.lastupdate);
    temp.mudmod  = clientr.("muditem_" @ temp.row.wname)[12];
    if (temp.updated == temp.mudmod) {
      items.remove(temp.row.arc);
    } else {
      temp.item_updates.add(temp.row.arc);
    }
  }
  // Mass Item Data Update
  if (temp.item_updates.size() > 0) {
    temp.clause = SQL.generateInClause("arc", temp.item_updates); 
    temp.query  = "SELECT *
                   FROM MUDItems
                   WHERE " @ temp.clause;
    temp.data  = SQL.executeSQL2("items", temp.query, true);
    for (temp.row: temp.data.rows) {
      clientr.("muditem_" @ temp.row.wname) = {
        temp.row.wname, temp.row.type, temp.row.subtype,
        temp.row.icon, temp.row.image, temp.row.gani,
        temp.row.min, temp.row.max, temp.row.mods,
        temp.row.req, temp.row.speed, temp.row.price,
        int(temp.row.lastupdate)
      };
      items.remove(temp.row.arc);
    }
  }
  // Items not found in DB
  // Load Individually from File
  // Cache it in DB
  for (temp.item: items) {
    MUDLoadItem("", temp.item);
  }
}

public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
} 


I get pretty much the same performance as loading it directly from a Cache DB every-time with significantly less memory usage.

If I wanted to put a new system in I would go with salesman's initial suggestion though. clientr variables would merely act as a 'cached' reflection of your database. Just make sure your DB is indexed properly.

Really though just put something basic together the item system is probably the easiest system to put together.

I like putting the data in an array and write accessors to make sense of the data in your script. I.e.

clientr.itemdata.id = {name, type, value};

PHP Code:

//#CLIENTSIDE
function getItemName(id) {
  return clientr.itemdata.(@id)[0];
}

function getItemType(id) {
  return clientr.itemdata.(@id)[1];
}

function getItemValue(id) {
  return clientr.itemdata.(@id)[2];
}