[wp-hackers] Time for a wp_post_relationships table?

[Otto]

On Mon, Aug 2, 2010 at 9:39 PM, Mike Schinkel
<mikeschinkel at newclarity.net> wrote:
> Okay, I guess asking to discuss on trac was a fail.  Live and learn.

Trac comments is better for discussion of patches and code and such.
Not for ideas.

However, I'll play devil's advocate.

> 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?

There is no use case in core that requires post-to-post relationships
in this way. Even if comments become a post type, it's a many-to-one
relationship that can easily use the post_parent field. For special
plugin cases, they should devise their own methods for doing what they
want. Hell, they will anyway.

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

They won't interoperate anyway. The proposed table doesn't specify
what the given relationships *mean*. What does it mean if I have a
bunch of posts related to other posts and to taxonomies? No idea. It's
just a big table of numbers, you can interpret it any way you choose.

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

Generally we don't put things in core just so plugins can use them.
There needs to be some kind of core use of this table. Making a table
that will be empty unless you use a specific plugin is silly.

If plugins want to interoperate, they can do that now by having plugin
developers talk to one another.

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

I don't see how taxonomy has anything to do with post-to-post
relationships, but then I also admit that I don't understand any
significant use for the the table as proposed. A fuller and more
complete explanation would be preferred. The links you gave were light
on detail.

> 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?

Post meta could be used for the majority of cases, and I don't see
that there's any scaling issues whatsoever there. The meta_value
doesn't need to be indexed to join on it, when you're using the
meta_key as a selection mechanism. How many post relationships can a
post have? 1000 to 1000? What would be the point?

What *is* the point, anyway? What does this table *mean*? What data
does it hold? Speaking abstract is fine, but I don't see the use for
the data itself, because I see no use cases here at all. What is a
specific use case that I can point to and say "this is why we need
this"?

-Otto