[wp-hackers] Settings pages

[Nathan Rice]

Mike,
You're obviously right ... there are always exceptions to the rules, and in
the cases you described, there is obvious need for multiple entries in the
options db.

But for most, we're talking about very simple data (boolean, ID arrays, html
code, etc.) that would never warrant multiple entries in the options db.

Sorry, didn't mean to indicate that there was never a reason to use multiple
entries.

My Website
http://www.nathanrice.net/

My Twitter
http://twitter.com/nathanrice


On Sat, Jun 13, 2009 at 6:32 AM, Mike Schinkel
<mikeschinkel at newclarity.net>wrote:

> "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?
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>