Best practice for multi-table caching (to list)

[Skylos]

Heh, missed the list on this one, wouldn't want to deprive ya'll of a
good possibility to slap me down!

Skylos

...

abstract the updates and retrievals to a class that handles data
retrieval/storage - whenever jonnyboy does anything that involves it,
the class will automatically update the cache to be most current based
on the logic that joe-bob coded in the module.

That way you normalize the code - there is only one place where a
title gets updated anywhere in the code - only one place possible - in
the module that has a method called 'update_title' or something - so
you don't have to worry about the memcached or whatever db or funky
shit that might be goin on in the background - all you have to do is
update the title.

Code functionality once.  Use it many times.

;)

Skylos


On Wed, 23 Feb 2005 16:52:46 -0800, Jon Drukman <jsd at cluttered.com> wrote:
> SELECT a.*, b.*, c.*
> FROM TOPIC a, BOARD b, MSGS c
> WHERE c.MessageID = 1 AND a.TopicID = c.MessageID AND b.BoardID = c.BoardID
>
> How to memcache this data?
> - set_cache("MY_WACKY_QUERY_1", data) note: 1 is the message id
>
> Now there are lots of places in the code which update these 3 tables
> (independantly of each other), so we need to del_cache("XXX_1") whenever
> any of these updates and inserts affect MSGS, TOPIC, or BOARD data for,
> or relating to, the message with id 1.
>
> For example: say the msg with an id of 1 exists in a board with an id of
> 4. And later someone edits board 4 and changes the title. Then someone
> comes along and requests the wacky query data stored in memcache under
> 'MY_WACKY_QUERY_1' - well sir, they will be seeing the wrong board
> title. So, we edit the code where the board title is updated to delete
> "MY_WACKY_QUERY_x" where x = all msgs within that board. We do the same
> thing for TOPIC and any other place where topic, board, and msgs is
> updated. It works, but what a pain. Plus when joe-bob is done coding and
> johnny-boy comes along and decides to add some code, johnny boy might
> easily screw up and not even know to delete memcache keys when certain
> tables are updated... Heck, for that matter joe-bob might even forget
> himself if he has a memory like mine.
>
> Is there an easy solution? I'm stumped... Any ideas?
>
>