[wp-hackers] Time for a wp_post_relationships table?

[Mike Schinkel]

On Aug 2, 2010, at 9:22 PM, Otto wrote:
> This seems like something you could just use meta tables for. Not sure
> why this needs a whole other table.

On Aug 2, 2010, at 10:07 PM, John Blackbourn wrote:
> On Tue, Aug 3, 2010 at 1:16 AM, Mike Schinkel
> <mikeschinkel at newclarity.net> wrote:
>> [1] http://lists.automattic.com/pipermail/wp-hackers/2010-July/033510.html
> 
> This link doesn't provide much information about why we need a posts
> relationship table. Are there some other more relevant posts in that
> thread with details?

Okay, I guess asking to discuss on trac was a fail.  Live and learn.

I'm not sure exactly why Michael Pretty decided he'll use a custom table in the future but I'll give you the reasons why I'm starting to think it makes sense, starting with *least* important to most  (Michael, if you are listening please do let us know!)

1.) Post-to-post relationships are a special pattern that has extremely broad applicability across many use-cases.  Having a special purpose functionality to handle common use-cases makes sense much like why foreach() exists in PHP even though for() would have addressed every use-case where foreach() is used. Same here, we could use other alternatives but post-to-post relationships are so fundamental why delegate that use-case to generic functionality?

2.) If we delegate this to a case of "just use post meta" then we'll find different plugins will implement relationships different, i.e. using different meta_keys and thus won't interoperate.

3.) With the inclusion of a wp_post_relationships table we'll lay the groundwork for being able to add functions to core like but not limited to get_related_posts($post_id) as well as admin UI functionality to interact with related posts. We'll also lay groundwork for plugins to interoperate.

4.) Using taxonomy mirrored posts means ensuring nothing accidentally changes one of them without changing the other.  With plugins that can add/update/delete posts or taxonomy terms independently, especially via direct SQL, it gets very hard to ensure they don't get out of sync.

5.) Using postmeta to store post-to-post relationships means storing foreign keys in non-indexed longtext meta_value field or embedded in the meta_key field. That's not too bad if you are looking up via post_id but it's awful if you have a large database with an even larger number of relationships and you need to do a select or worse join to the post->ID that is stored in meta_value.  It's absolutely not scalable and while #1 thru #4 are all debatable I'm pretty sure everyone can understand how this is unworkable for sites that may need to scale one day? 

While I think we could come up with arguments to add many other tables too I'm not sure we could find another pattern that would be as relevant as a table that allows relating posts since posts are the core of the entire system.

Thoughts?

-Mike
P.S. Or course I've learned anytime I make an absolute statement like #5 on this list I'm certain to get people to contradict me, so bring it on! :)