[wp-hackers] Settings pages

[Mike Schinkel]

"Ptah Dunbar" <pt at ptahd.com> wrote:
> Thanks for the tips NR. I was already afraid of that after snooping 
> around the core a little bit more. And yes, everything's going into 
> one fat array (you wouldnt catch me dead adding options all over 
> the place).

Something to consider. "Never" shouldn't be a hard and fast rule.  If you are storing record IDs or other items that can be used to uniquely identify a record you should consider putting them in their own option, especially if the information would be needed as a linking table in a SQL query.

As a case-in-point NextGen Gallery stores most options in "ngg_options."  One of the thing NGG stores in ngg_options is which galleries are part of which albums. My client wanted to display the albums in descending order of those that had the most recently added photos. It was completely impractical from a performance perspective to do it in PHP and not possible as delivered to do it in SQL.  

OTOH, had NGG used separate option entries for each album which violates NR's rule it would have been practical to do in SQL. For example, assuming album_id=7 had three (3) galleries with ids of 2, 5 & 17:

update_option('ngg_galleries_for_album_7','2,5,17');

Then I could have used the following SQL to get a list of photos in reverse chronological order:

SELECT a.id,a.name, g.gid,g.name, p.* 
FROM wp_ngg_album a
INNER JOIN wp_options o ON CONCAT('ngg_galleries_for_album_',a.id)=o.option_name
INNER JOIN wp_ngg_gallery g ON FIND_IN_SET(g.gid,o.option_value)
INNER JOIN wp_ngg_pictures p ON g.gid=p.galleryid
ORDER BY p.imagedate DESC

So even though most of the time you should put all options into one array you shouldn't ALWAYS do it that way. As with anything you should consider the situation and potential use-cases before deciding what makes the most sense.  IOW, "Always put all options in a serialized array" should be more of a guideline than a rule.

Just so you know how I solved it I had to hack NGG to include a linking table for "album_galleries" and I had my hacked version of NGG update that table right after it updated ngg_options. Here's a SQL query that shows how it worked (note it's much cleaner than the wp_options version above):

SELECT a.name, g.name, p.* 
FROM wp_ngg_album a
INNER JOIN wp_ngg_album_galleries ag ON a.id=ag.aid
INNER JOIN wp_ngg_gallery g ON ag.gid=g.gid
INNER JOIN wp_ngg_pictures p ON g.gid=p.galleryid
ORDER BY p.imagedate DESC

FWIW, it's often better especially for performance to do it with a linking table than to put in the options table the way I suggested.  OTOH, there is conceptual and maintenance overhead to adding tables so sometimes it's just better put into the wp_options table rather than have tens of simple linking tables cluttering up the database. 

But whatever the case for plugin/theme developers who've not had experience with relational database 3rd normal form having a rule that says "Always put all options in a serialized array" can result in doing so just because it's "a rule" instead of considering the use-case (see Wikipedia for "cargo cultist.") When that happens the plugin/theme author paints their user into a nasty corner and forces them to hack it up, as I had to do with NGG.

HTH.

-Mike Schinkel
Custom Wordpress Plugins
http://mikeschinkel.com/custom-wordpress-plugins

P.S. BTW, I keep meaning to send Alex Rabe my updates to NGG for hopeful inclusion in his next version.  Alex, are you on this list?