[bbDev] bbPress meta data storage

[Michael D Adams]

Currently, bbPress stores meta data using four DB columns:

meta_id, topic_id, meta_key, meta_value

However the bb_update_meta requires the (topic_id, meta_key)  
combination to be unique. (There can be at most one meta datum with  
meta_key = "snarfer" for each topic), so there's no point in having  
the meta_id column.

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).

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).

Any other thoughts?
Michael