Separate table or Properties field for CRC

In the docs for creating a Custom Resource Class, it says

create a separate related table to join into, or use Revolution 2.2.1+'s new properties field to store extra data.

My question is, which should you do, and under what circumstances -- most importantly, which is faster?

My guess would be that the Properites field would be faster for a small number of extra fields, whereas a separate table would be faster for a large number of extra fields, but I have no idea where the cutoff would be or what other considerations I should take into account.

Using both would be the best choice. That way, you can have normalized, searchable tables with summarized pre-processed values stored in the properties field for use when reading.

Wow, thanks for the quick reply! I'll get back to you once I understand what it means... ;-)

Seriously, I'm impressed by your ability to pack a lot of information into a short sentence! I think I get the general idea though, let me see if I understand:

• When a resource is saved, you store extra data in normalized custom tables.
• Then (still part of the save event) you do any pre-processing, cross-referencing or whatever that you would normally do prior to rendering the actual output (for example, you might pull data from the users table to display information about the author of a blog post).
• Store the processed values, ready to be output directly to the front end, in the properties field using the new properties api methods.
• When you render or list the resource, pull the processed values directly from the Properties field - that way you don't have to look anything up in separate tables or do any pre-processing, you just dump the values straight onto the page.

Is that the general gist of it? Sorry -- just want to make sure I'm getting your drift...

That is the precise gist of it. Thanks for translating for those who are not so impressed by my brevity.

OK, but a problem has occurred to me. How do you update the properties field if the values in your tables change? I suppose you could attach something to the clear cache event...

Quote from: esnyder at Apr 09, 2012, 03:04 PM

OK, but a problem has occurred to me. How do you update the properties field if the values in your tables change? I suppose you could attach something to the clear cache event...

You would manage this when saving the Resource, based on whatever requirements you have for the controllers and processors for the CRC.

If that was not being done in the UX for editing the CRC Resources, you could use the cache event.

Override the save() method in your custom table's xPDOObject class, and update the properties related modResource class after calling parent::save() in the related class.

Awesome, thanks guys!

This is a really cool idea with lots of possibilities -- like for images, you could store the original image URL in a table (along with other image properties like EXIF data), use PHPthumb to render a thumbnail, then store the thumbnail URL in the properties field. That would be blazing fast on the front end. Then if you change the image, you re-render the thumb using the save() method for the images table, so you could even change the image from somewhere other than the resource itself (like by using some sort of batch updater or something) and still update the resource properties. Very cool...