[bbDev] bbPress meta data storage

[Dougal Campbell]

Michael D Adams wrote:
> > [....] 

> Ideally the code and the DB schema would match.  I see two options:
>
> 1. Keep the DB the same and change the bbPress code to allow multiple
> entries per topic with the same meta_key.
>
> Advantages:
> It's what WordPress does.
> It would allow people to store arrays of information in meta data item
> in the array per row in the meta table.  That's great for SELECTs in
> certain, fairly specific situations. (For example, user favorites are
> currently stored redundantly both in topic meta and in user meta. 
> With this option, we would only have to store things in one place.)
>
> Disadvantage:
> Less efficient DB.
> Slower queries.
> Significantly heavier bbPress code (in getting and updating the meta
> data).
> More difficult to use for plugin authors (WP meta data is pretty
> obnoxious for several reasons, this being one of them).
>

When I first designed the post_meta stuff for WP, I consciously chose to
allow duplicate keys, for maximum flexibility. I didn't want to limit
what plugin authors would be able to do with the metadata. But as you
note, it does make things more complicated, since it forces an
additional level of data abstraction. The burden of functionality could
easily have been shifted to the meta_value by simply requiring coders to
serialize/unserialize when they needed to store array types. It's yet
another case of hindsight being 20/20.

> 2. change the DB to only three columns:
> topic_id, meta_key, meta_value with a PRIMARY KEY of (topic_id, meta_key)
>
> and keep the bbPress code the same.
>
> Advantages:
> more efficient database.
> simple, clean code (for grabbing and updating meta data).
> Really cheap queries (especially if we bump the MySQL requirements to
> 4.1)
>
> Disadvantages:
> Arrays must be stored as they are now: as serialized data in one row
> of the meta table.  This makes some SELECTs essentially impossible
> (hence the redundancy in user favorites storage).
>
> I favor option 2.  I think it's a faster DB scheme and I think its
> disadvantages aren't terribly disadvantageous (for example, the
> duplication of user favorites data makes it a little harder to
> maintain the data, but we probably have to duplicate it in *either*
> case for the sake of read efficiency).

While part of me feels like it should mirror the WP implementation, for
consistency, another part of me says that duplicate meta keys are
probably an edge case in real-life use, and probably not worth the
trouble. I vote for option 2.

I even wonder how much trouble it would cause if we made a similar
change in WP. We could make the upgrade process check for duplicate keys
and combine them into a single key with a serialized array value, then
modify the API functions to change how they access the data, while
maintaining the old data structures.

-- 
Dougal Campbell <dougal at gunters.org>
http://dougal.gunters.org/