Time for a wp_post_relationships table?

[Mike Schinkel-5]

Hi all,

Is it time to discuss a wp_post_relationships table?   Michael Pretty made some rather insightful remarks[1] on the topic last week and I've been bumping into the same:

On Jul 26, 2010, at 10:30 PM, Michael Pretty wrote:
> We have a couple more projects lined up where we need a many to many relationship between objects and have decided that for us the best option is to bite the bullet and create a new table.  Its something we usually leave as a last resort, but at this point, that seems like the best option because of the issues you mentioned.  I haven't yet worked out all the details of how it is going to work other than a table that stores parent_id, child_id and relationship_type, with lots of caching.  At this point it just seems like it is going to be the only way to provide the many to many relationship that is both stable and provides decent performance.

So I submitted a "straw-man proposal"[2] as a ticket on trac[3].  

If you are interested in discussing it, PLEASE let's do it on the trac ticket[3].

-Mike

[1] http://lists.automattic.com/pipermail/wp-hackers/2010-July/033510.html
[2] http://en.wikipedia.org/wiki/Straw_man_proposal
[3] http://core.trac.wordpress.org/ticket/14513

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Otto-19]

This seems like something you could just use meta tables for. Not sure
why this needs a whole other table.

-Otto



On Mon, Aug 2, 2010 at 7:16 PM, Mike Schinkel
<mikeschinkel@...> wrote:

> Hi all,
>
> Is it time to discuss a wp_post_relationships table?   Michael Pretty made some rather insightful remarks[1] on the topic last week and I've been bumping into the same:
>
> On Jul 26, 2010, at 10:30 PM, Michael Pretty wrote:
>> We have a couple more projects lined up where we need a many to many relationship between objects and have decided that for us the best option is to bite the bullet and create a new table.  Its something we usually leave as a last resort, but at this point, that seems like the best option because of the issues you mentioned.  I haven't yet worked out all the details of how it is going to work other than a table that stores parent_id, child_id and relationship_type, with lots of caching.  At this point it just seems like it is going to be the only way to provide the many to many relationship that is both stable and provides decent performance.
>
> So I submitted a "straw-man proposal"[2] as a ticket on trac[3].
>
> If you are interested in discussing it, PLEASE let's do it on the trac ticket[3].
>
> -Mike
>
> [1] http://lists.automattic.com/pipermail/wp-hackers/2010-July/033510.html
> [2] http://en.wikipedia.org/wiki/Straw_man_proposal
> [3] http://core.trac.wordpress.org/ticket/14513
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers@...
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Mike Schinkel-5]

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.


Discuss on trac?

http://core.trac.wordpress.org/ticket/14513

(or is it better to discuss here. I'm guess I'm not sure.)

-Mike

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[John Blackbourn]

On Tue, Aug 3, 2010 at 1:16 AM, Mike Schinkel
<mikeschinkel@...> 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?
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Mike Schinkel-5]

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@...> 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! :)





_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Otto-19]

On Mon, Aug 2, 2010 at 9:39 PM, Mike Schinkel
<mikeschinkel@...> 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
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Mike Schinkel-5]

On Aug 3, 2010, at 12:34 AM, Otto wrote:
> However, I'll play devil's advocate.

I expect no less from you, though I have come to appreciate it. :)

In the same vein I'll respond. I currently see value but am open to having my opinion changed. So it's not a "fight to the death" on the topic but to have an honest debate.

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

Not yet, anyway though I could easily see one being added at some point in the future such as a related posts selection metabox that could be triggered by adding something like "related-posts-{$post_type}" in the "supports" section of register_post_type().  This would be helpful for every single site I've worked on since 3.0 was in beta (and before, even.)

> Even if comments become a post type, it's a many-to-one
> relationship that can easily use the post_parent field.

Yes, post_parent works well for one-to-many, but can't handle many-to-many.  It also can't handle it when a post can be on the many end of one-to-many but related to more than one post type (see examples below.)

> For special plugin cases, they should devise their own methods for
> doing what they want. Hell, they will anyway.

Why would we want to devise their own methods rather than use a standard approach built into core?  (It's an honest question.)

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

You are getting too use-case specific; they don't need to specify what the relationships mean only that they are related. Plugins can add the "meaning" layer but WordPress should probably offer the core relationship support.

There are many generic functionalities that would have value.  For example, you could have a plugin that managed related posts in a grid editor and a use case for that would be actors in a movie and the grid would let the user add/edit actors for the movie and add annotate the name of their character from the movie.  Then another plugin could provide a "related posts" widget which could be used to display the actors on the movie page and the movies on the actor page.  With a standardized table like this those plugins could (and often would) be developed separately and unaware of the other.  Without such a table the only way to get those functionalities would be if one plugin author developed it all, if the two collaborated, or if one built on the other; all of which are much less likey scenarios.

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

Well, I don't think it is silly but our opinion differ.  Taken in a vacuum, that's like saying that if electric appliance vendors want their appliances to work they can collaborate with homebuilders to make sure that the homes are built with their proprietary connector of powering the appliances.  Fortunately for us that need not be the case because the industry had the forethought to standardize plugs and outlets (at least within the US, can't speak for everywhere else.)

Or another analogy would be to say "If the browser vendor wants to interoperate with the web server, they can talk to one another."  If they had used the above logic, where would the web be now?

I think maybe I simply value standardization as a stepping stone for greater things and you don't (right?) and that difference in how we value standardization colors any discussion we have related to it.

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

Michael Pretty implemented taxonomy mirrored posts in a project and mentioned it on the list.  I then asked for some help implementing from anyone who had done so (Michael didn't offer help but then I didn't directly ask him and he was under no obligation to help anyway.) So I implemented a taxonomy-mirrored posts plugin asked a question about it on the list to which Michael replied that he had come to the conclusion that it caused two many problems and that he was next going to use a custom table even though typically he would never add a new table.

The use-cases for post-to-post are probably not very apparent when using WordPress only as a blog but become much more apparent when one starts using it as a CMS with lots of different data types.  I'll give you numerous examples where I've needed it in the past several months (#1 thru #3 are actual projects I've worked on, #4 & #5 are hypothetical):

1.) A law firm website has attorneys, practice areas, articles, case studies, and events/presentations.  Each attorney is in one or more practice areas.  Each article and each case study can be attached to one or more attorneys and relevant to one or more practice areas.  Each event/presentation can have one or more attorneys presenting and can be represented one one or more practice areas' calendars.

2.) A conference website can have sessions, sponsors, presenters, time slots and rooms. A session can have one or more presenters and be presented in one or more time slots and one room per time slot ,and a presenter can represent a sponsor or not.

3.) A website listing restaurant menus can have restaurants, locations, menus. A restaurant can have one or more locations and one or more menus (breakfast, lunch, dinner, catering, etc.) A menu can apply to all locations for a restaurant or only a specific location.  

4.) A movie website could have movies and person-roles where a person-role could be an actor, producer, cinematographer, casting, film editing, costumer, set designer, composer, etc.

5.) A hospital website has doctors and departments.  Each department can have multiple doctors and each doctor can work in multiple departments.

6.) A radio station website could have stations and disc jockey's where a station could have multiple disc jockeys and each disc jockey could appear on multiple stations (given how radio works these days.)

I could actually give you many other examples if you need it; there is an example for almost every business that could potentially use WordPress for it's website (vs. just it's blog.)

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

It's not the number of post relationships that a *specific* post has, it's the *total* number of records with the matching meta_key in the wp_postmeta table.  Let's assume an IMDB.COM clone with only 1,000 movies listed (According to Google by searching "inurl:www.imdb.com/title/" IMDB.com has a jaw dropping 73,500,000 movies. Really, there are that many movies?  Wow.)  

1000 is trivial, but I didn't want to say 10,000+ because I didn't want you to argue with the number.  

So we add a postmeta field to relate post_id to the movie's post->ID, meta_key='actor', and meta_value relates to actor's post->ID.  Now let's assume that each post has 10 post_meta fields for "actor" on average. So that's 10,000 postmeta records.  Want a list of actors for a movie?    Here's the SQL:

$sql = $wpdb->prepare("
SELECT
        actor.post_title AS actor
FROM
        wp_posts actor
        INNER JOIN wp_postmeta movie_actor ON actor.ID=movie_actor.meta_value AND movie_actor.meta_key='actor'
WHERE
        movie_actor.post_id=$d", $actor_id);

MySQL EXPLAIN gives us the following. Not too bad; we have to do a 10,000 record scan but at least it is on an 8 byte key:

*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : movie_actor
type         : ref
possible_keys: post_id,meta_key
key          : post_id
key_len      : 8
ref          : const
rows         : 10,000
Extra        : Using where
*************************** 2. row ***************************
id           : 1
select_type  : SIMPLE
table        : actor
type         : eq_ref
possible_keys: PRIMARY
key          : PRIMARY
key_len      : 8
ref          : movie_actor.meta_value
rows         : 1
Extra        : Using where
2 rows in set

OTOH, want a list of movies for an actor? Here's the query (almost the same):

$sql = $wpdb->prepare("
SELECT
        movie.post_title AS movie
FROM
        wp_posts movie
        INNER JOIN wp_postmeta movie_actor ON movie.ID=movie_actor.post_id AND movie_actor.meta_key='actor'
WHERE
        movie_actor.meta_value=$d", $movie_id);

And here's the MySQL EXPLAIN.   10,000 records scanning a 768 byte key (and "Using where.")  YEOWCH!

*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : movie_actor
type         : ref
possible_keys: post_id,meta_key
key          : meta_key
key_len      : 768
ref          : const
rows         : 10,000
Extra        : Using where
*************************** 2. row ***************************
id           : 1
select_type  : SIMPLE
table        : movie
type         : eq_ref
possible_keys: PRIMARY
key          : PRIMARY
key_len      : 8
ref          : movie_actor.post_id
rows         : 1
Extra        :
2 rows in set

Now what if we have 10,000 movies and 25 postmeta fields per movie?  Now we are talking abut scanning 768 byte fields for 250,000 records for *each* query like the latter one.

To make matters worse, what if we now need to do a join?  What if we want a query that shows which actors have worked with other actors?  Now we have to do a table scan during a join.

But what if we had the second query using the proposed wp_post_relationships table:

SELECT
        movie.post_title AS movie
FROM
        wp_posts movie
        INNER JOIN wp_post_relationships movie_actor ON movie.ID=movie_actor.parent_id
WHERE
        movie_actor.post_id=22

And MySQL EXPLAIN tells us we match one row with one row and no "Using where." This is about as good of MySQL performance as you can get.  For even moderate sized data sets this will be orders of magnitude faster than scanning a 768 byte key using where.  So we are talking about sites that will start slowing down significantly with almost trivial number of posts.

*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : movie_actor
type         : ref
possible_keys: PRIMARY,post_id
key          : post_id
key_len      : 8
ref          : const
rows         : 1
Extra        :
*************************** 2. row ***************************
id           : 1
select_type  : SIMPLE
table        : movie
type         : eq_ref
possible_keys: PRIMARY
key          : PRIMARY
key_len      : 8
ref          : wp30.movie_actor.parent_id
rows         : 1
Extra        :
2 rows in set (0.02 sec)

Still think that is "no issue whatsoever?"  

If yes, here's some good reading:

-- http://www.slideshare.net/techdude/how-to-kill-mysql-performance
-- http://joinfu.com/presentations/understanding_query_execution.pdf
-- http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-1/2/

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

In a word if it's all about blogging only, most of this is unimportant.  

However, once CMS requirements are added into the mix it becomes important.  Examples #1 thru #6 above are specifics, not abstracts.  Not all of sites #1 thru #6 will need to scale but some of them will and especially #3 and #4.  

And all of them would benefit from standardized post-to-post relationships whether you appreciate the value of such or not. :)

-Mike
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Casey Bisson-2]

Mike,

I'm actually a bit sympathetic to your question, but many of the
relationships you describe are taxonomy-style relationships. I think what
you're really looking for is how to durably attach a post (of any type) to a
specific term/taxonomy pair. Perhaps an extra field for post_id in the
term_taxonomy table is what you need?

Some WP themes and sites have used the category/taxonomy description field
very well. I see Michael Pretty's scenario to be an extension of that (more
sophisticated, but not an entirely different animal). I can imagine that we
could enable many uses of WP by moving that description field into a new
post type and replacing the that field in the table with a post_id number
that links them.

--Casey

On Tue, Aug 3, 2010 at 4:05 AM, Mike Schinkel
<mikeschinkel@...>wrote:

The use-cases for post-to-post are probably not very apparent when using

> WordPress only as a blog but become much more apparent when one starts using
> it as a CMS with lots of different data types.  I'll give you numerous
> examples where I've needed it in the past several months (#1 thru #3 are
> actual projects I've worked on, #4 & #5 are hypothetical):
>
> 1.) A law firm website has attorneys, practice areas, articles, case
> studies, and events/presentations.  Each attorney is in one or more practice
> areas.  Each article and each case study can be attached to one or more
> attorneys and relevant to one or more practice areas.  Each
> event/presentation can have one or more attorneys presenting and can be
> represented one one or more practice areas' calendars.
>
> 2.) A conference website can have sessions, sponsors, presenters, time
> slots and rooms. A session can have one or more presenters and be presented
> in one or more time slots and one room per time slot ,and a presenter can
> represent a sponsor or not.
>
> 3.) A website listing restaurant menus can have restaurants, locations,
> menus. A restaurant can have one or more locations and one or more menus
> (breakfast, lunch, dinner, catering, etc.) A menu can apply to all locations
> for a restaurant or only a specific location.
>
> 4.) A movie website could have movies and person-roles where a person-role
> could be an actor, producer, cinematographer, casting, film editing,
> costumer, set designer, composer, etc.
>
> 5.) A hospital website has doctors and departments.  Each department can
> have multiple doctors and each doctor can work in multiple departments.
>
> 6.) A radio station website could have stations and disc jockey's where a
> station could have multiple disc jockeys and each disc jockey could appear
> on multiple stations (given how radio works these days.)
>
> I could actually give you many other examples if you need it; there is an
> example for almost every business that could potentially use WordPress for
> it's website (vs. just it's blog.)

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Otto-19]

On Tue, Aug 3, 2010 at 3:05 AM, Mike Schinkel
<mikeschinkel@...> wrote:
> Why would we want to devise their own methods rather than use a standard approach built into core?  (It's an honest question.)

Two reasons.
1. Competition breeds better solutions.
2. The generic case is not a good fit for all use cases.

> You are getting too use-case specific; they don't need to specify what the relationships mean only that they are related. Plugins can add the "meaning" layer but WordPress should probably offer the core relationship support.

The relationships have to be meaningful for there to be
interoperability between differing implementations.

> I think maybe I simply value standardization as a stepping stone for greater things and you don't (right?) and that difference in how we value standardization colors any discussion we have related to it.

I think standardization is a good thing, but the standard must be
well-defined and have a well-defined purpose for it to be useful. This
particular case just seems very vague and hand-wavy to me.

> The use-cases for post-to-post are probably not very apparent when using WordPress only as a blog but become much more apparent when one starts using it as a CMS with lots of different data types.  I'll give you numerous examples where I've needed it in the past several months (#1 thru #3 are actual projects I've worked on, #4 & #5 are hypothetical):
>
> 1.) A law firm website has attorneys, practice areas, articles, case studies, and events/presentations.  Each attorney is in one or more practice areas.  Each article and each case study can be attached to one or more attorneys and relevant to one or more practice areas.  Each event/presentation can have one or more attorneys presenting and can be represented one one or more practice areas' calendars.
>
> 2.) A conference website can have sessions, sponsors, presenters, time slots and rooms. A session can have one or more presenters and be presented in one or more time slots and one room per time slot ,and a presenter can represent a sponsor or not.
>
> 3.) A website listing restaurant menus can have restaurants, locations, menus. A restaurant can have one or more locations and one or more menus (breakfast, lunch, dinner, catering, etc.) A menu can apply to all locations for a restaurant or only a specific location.
>
> 4.) A movie website could have movies and person-roles where a person-role could be an actor, producer, cinematographer, casting, film editing, costumer, set designer, composer, etc.
>
> 5.) A hospital website has doctors and departments.  Each department can have multiple doctors and each doctor can work in multiple departments.
>
> 6.) A radio station website could have stations and disc jockey's where a station could have multiple disc jockeys and each disc jockey could appear on multiple stations (given how radio works these days.)

I don't see how any of these relate to a table which relates posts to
taxonomies (and to each other). Posts get related to terms inside
taxonomies, not to taxonomies themselves.

The cases above seem like you want to be able to relate terms in one
taxonomy to terms in another taxonomy, not to relate posts to whole
taxonomies. IN which case I'd say your initial definitions for what
constitutes a post vs. a term are wrong. If you do want to make
something where a "thing" has both a post and a term for that thing,
then you should do it with a custom post type and tie the term from
the taxonomy to it. Let's consider a simple case of photo management.

Photos are posts (attachments in our specific case, but let's just say
"photos"). They have people in them. Every person is a term in the
"people" taxonomy.
But people have other information about them too. So we make a
"people" custom post type to hold them. Maybe a bio, avatar, etc. To
associate the person to the taxonomy, we simply give that post of them
in "people" the term in the "people" taxonomy, thus relating the two.
So now, if I'm looking at a photo, I can see a list of the people in
the photo by looking at the terms in the people taxonomy. Click on one
of those and I can see all the posts associated with that person. Only
one of those posts is of the "people" type, and that's the one that
gives more information about that person.

My point is that posts should connect to terms, and relate to other
posts by virtue of having the same terms in a taxonomy, even across
post-type  boundaries. This is a much more flexible way of connecting
posts to other posts based on specific shared characteristics.

For your lawyer example (first one):
Post types: attorney, article, case study, event.
Taxonomies: attorney, practice area.

Any of those post types can have terms attached to it from both
taxonomies. There's a specific restriction (in your plugin code) to
only allow one attorney post type per attorney term and only one
attorney term in each attorney post type.

The way you seem to want to do it is to have a whole taxonomy for each
attorney (I think), which is difficult and makes little sense to me. A
taxonomy is general, a person is specific.

> It's not the number of post relationships that a *specific* post has, it's the *total* number of records with the matching meta_key in the wp_postmeta table.

Actually, it is. The postmeta table is indexed by post_id too. If
you're searching through postmeta, you should always be tying to a
specific post.

You are correct that postmeta is a crap way of relating posts to one
another. But that is exactly what taxonomies and terms were designed
to do. I think that because you're defining things as a term instead
of a post type (or vice-versa), I think you're ending up with a
distorted view of what the necessary queries should be.

In your movie example, a movie would be a post type. So would an
actor. These would hold info about them. But they'd both also be
taxonomies (movie and actor). How would the two different items be
related? Term_relationships. A Movie post type would have actors
assigned to it as terms related to it. And it'd have one term from the
movies taxonomy related to it as well. An actor would be reversed,
he's in a bunch of movies (and so is tagged with all those movie
terms), and he's an actor (and so has his actor term tagged to him
too).

This particular case involves no postmeta, because it's using terms
the way they were meant to be used. Terms are there to relate
different posts together in a way that has meaning (by virtue of them
all being in a specific taxonomy). And this meaningful relationship
can be done via many different ways, including many to many.

-Otto
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Otto-19]

On Tue, Aug 3, 2010 at 3:05 AM, Mike Schinkel
<mikeschinkel@...> wrote:
> 4.) A movie website could have movies and person-roles where a person-role could be an actor, producer, cinematographer, casting, film editing, costumer, set designer, composer, etc.

I think I'm starting to see what you're talking about here, and why
you need the table the way you want it.

In your design for this one, for example, movies and persons would
both be a custom post type. The person post would then be associated
directly to the movie post and you'd create a "role" taxonomy with
types of relationships to associate the two together in a meaningful
way.

The problem is that I don't think this is the right way to do it. It
seems to me that this is over-generalizing. Instead of "role" being a
taxonomy and having things like "actor" be terms in it, I think using
"actor" as a taxonomy is a better way to go.

Consider your setup. If I wanted to display a list of credits for a
movie, I have to go pull all the related posts for that movie and
possibly parse them to determine names and such. It's a select into
the posts table based on the join, followed by a lot of code to do
parsing.

But if each role is a taxonomy itself, then the taxonomy name and
terms both have displayable versions that I can use directly. It's no
worse than displaying tags. And if I do want to drill deeper, I can
get related posts by virtue of them sharing terms, and use custom post
types to determine what each type of post is.

I don't think it's necessarily crazy to allow a "thing" to be both a
post type *and* a taxonomy as well. Managing your restrictions on a
per-post basis is simpler than having to select a large number of
posts and then filter through them in different ways for simple tasks.
Keeping the correct terms associated with the correct custom-posts is
not difficult if you abstract out the creation of the posts. For
example, when I make a new actor post, I'd automatically make the
proper term for that actor and auto-tag the post for him accordingly.
The plugin can do this. And then I simply don't give any way to remove
that term from the actor. The taxonomy can be completely managed
behind the scenes, the user doesn't have to manually manage it.

-Otto
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Daniel Cameron-3]

> My point is that posts should connect to terms, and relate to other
> posts by virtue of having the same terms in a taxonomy, even across
> post-type  boundaries. This is a much more flexible way of connecting
> posts to other posts based on specific shared characteristics.
>

We've approached one of latest projects with this exact method and I'm glad
we did because a post-meta option was a possible option.

We have about 10 post-types and a single taxonomy called 'nexus' that allows
us to connect any of our posts with each other. The purpose for the
connections is to build a custom menu of affiliated/associated posts. Also,
since we're using simple post titles for terms I've built a function that
uses wp_set_object_terms to automatically create the first affiliation on
publish.

If you want to know more I'm willing to share but it seems as though this
conversation is already heading in the right direction ( IMO ).

[ I invested too much time reading this thread to not comment a +1 :) ]
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Jeremy Clarke]

FWIW, I think a built-in system for associating metadata to taxonomy terms
would solve/simplify a LOT of these scenarios, and make it easier to use the
taxonomy system as intended without getting involved in post-on-post
relationships.

If actors/attorneys/people, in their term format, could have more than one
data field associated to them then you don't need the special single
page-type post about them to exist, you can just depend on the term listing
to show that content at the top with the related posts listed below. E.g.
Brad Pitt is a term in the actors taxonomy and the term has his bio, photo
etc. as meta fields. You then just use the get_category_link() style url to
link to his page, where you show all the info as it if was a post at the
top, along with other posts/post_types listed below.

This would make it a lot easier to have one core post type, in this case
movies, and let the relationships happen with the detailed and
metadata-powered taxonomy terms. This wouldn't solve the whole problem when
you have multiple post types and taxonomies that all have to be related
together,  but those scenarios will be a nightmare no matter what we do.

Of course, if done properly this would make terms feel a lot more like
posts, but why would that be such a bad thing? They already have a screen to
edit them like posts do, that screen just sucks right now.

Things that would make this a reality (mostly doable via. plugin right now,
but would be much more likely to be used if standardized):

   - Visual editor and other enhancements in term editing screen.
   - Standard means of associating meta to terms (term_meta table)
   - Easy API for defining default custom fields to show up in term editing
   UI (also needed for posts!)

IMHO this is a much more obvious need than post-to-post relationships and
most large cms-y sites could think of ways that term_meta would be useful.
It's an obvious need that also happens to solve a lot of the simpler class
of problem Mike is frustrated with.

--
Jeremy Clarke
Code and Design | globalvoicesonline.org
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Jake Goldman]

  Funny you should mention this.

I just released a plug-in that adds full, post-like meta data
functionality to taxonomy terms. I'd needed this in several projects
recently, and finally decided I should just package it up as an
independent plug-in.

I pinged Mark and Andrew about incorporating it in core, and didn't
receive any response.

http://wordpress.org/extend/plugins/simple-term-meta/

But I would be happy to write a patch once work on 3.1 resumes in
earnest to add this to core.

Jake


On 8/3/2010 12:27 PM, Jeremy Clarke wrote:

> FWIW, I think a built-in system for associating metadata to taxonomy terms
> would solve/simplify a LOT of these scenarios, and make it easier to use the
> taxonomy system as intended without getting involved in post-on-post
> relationships.
>
> If actors/attorneys/people, in their term format, could have more than one
> data field associated to them then you don't need the special single
> page-type post about them to exist, you can just depend on the term listing
> to show that content at the top with the related posts listed below. E.g.
> Brad Pitt is a term in the actors taxonomy and the term has his bio, photo
> etc. as meta fields. You then just use the get_category_link() style url to
> link to his page, where you show all the info as it if was a post at the
> top, along with other posts/post_types listed below.
>
> This would make it a lot easier to have one core post type, in this case
> movies, and let the relationships happen with the detailed and
> metadata-powered taxonomy terms. This wouldn't solve the whole problem when
> you have multiple post types and taxonomies that all have to be related
> together,  but those scenarios will be a nightmare no matter what we do.
>
> Of course, if done properly this would make terms feel a lot more like
> posts, but why would that be such a bad thing? They already have a screen to
> edit them like posts do, that screen just sucks right now.
>
> Things that would make this a reality (mostly doable via. plugin right now,
> but would be much more likely to be used if standardized):
>
>     - Visual editor and other enhancements in term editing screen.
>     - Standard means of associating meta to terms (term_meta table)
>     - Easy API for defining default custom fields to show up in term editing
>     UI (also needed for posts!)
>
> IMHO this is a much more obvious need than post-to-post relationships and
> most large cms-y sites could think of ways that term_meta would be useful.
> It's an obvious need that also happens to solve a lot of the simpler class
> of problem Mike is frustrated with.
>

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[scribu]

On Tue, Aug 3, 2010 at 7:35 PM, Jake Goldman <
wphackers@...> wrote:

> I just released a plug-in that adds full, post-like meta data functionality
> to taxonomy terms. I'd needed this in several projects recently, and finally
> decided I should just package it up as an independent plug-in.
>

It seems you're not the only one. There seem to be 3 plugins that do the
same thing ATM:

http://wordpress.org/extend/plugins/simple-term-meta/
http://wordpress.org/extend/plugins/taxonomy-metadata/
http://wordpress.org/extend/plugins/meta-for-taxonomies/


--
http://scribu.net
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Steve Bruner [SlipFire]]

In RoloPress, we added the relationship between Companies and Contacts
through custom taxonomies, and it worked great.   You can check it out the
demo here:http://demo.rolopress.com/  Or download it here:
http://rolopress.com/

Steve
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Lox-2]

Mike,

I'll point to that plugin that implements a post2post feature (from
trac) : http://wordpress.org/extend/plugins/posts-to-posts/

I know it uses a "post meta" approach but you could create such a
plug-in that creates your needed table and functions to use it... Then
any plug-in dev that need that approach can just install your plug-in
and benefit from it.

Otto, mirroring custom posts to taxonomies can be a bad ass, I mean,
creating needs to be handled, as well as modification and deletion. So
the question is: can't wordpress provides helpers for custom post
mirroring to a taxonomy? Like adding a "mirror_taxonomy_id" parameter
to custom post_type....

My two cents.

--
Lox
lox.dev@...
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Otto-19]

On Tue, Aug 3, 2010 at 1:41 PM, Lox <lox.dev@...> wrote:
> Otto, mirroring custom posts to taxonomies can be a bad ass, I mean,
> creating needs to be handled, as well as modification and deletion. So
> the question is: can't wordpress provides helpers for custom post
> mirroring to a taxonomy? Like adding a "mirror_taxonomy_id" parameter
> to custom post_type....

But what exactly are you mirroring? The title of the post? Some of the
content? A custom field? Posts are not taxonomies, which is why this
has to be left up to the specific implementation. How you define your
terms based on your post content is really your choice, sort of thing.

It's not really all that hard to do, you simply add the term to the
post with wp_set_object_terms when the post is published, and perhaps
modify it when the post is edited (delete will be handled
automatically, as when a post goes away, so does it's relationship
with its terms). You can probably use transistion_post_status to
handle both of those.

-Otto
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Lox-2]

2010/8/4 Otto <otto@...>:
> But what exactly are you mirroring? The title of the post? Some of the
> content? A custom field?

Eg: I have my movies and actors custom post_types. To make the
relation I _mirrors_ actors (title + short description) to a custom
taxonomy actors_tax and then apply the right tax term(s) to each
movie.

> Posts are not taxonomies, which is why this
> has to be left up to the specific implementation. How you define your
> terms based on your post content is really your choice, sort of thing.
>
> It's not really all that hard to do, you simply add the term to the
> post with wp_set_object_terms when the post is published, and perhaps
> modify it when the post is edited (delete will be handled
> automatically, as when a post goes away, so does it's relationship
> with its terms). You can probably use transistion_post_status to
> handle both of those.

It is not a hard but it is a tedious task. Well it is what it seemed
to me, a new WP developer.

Since WP 3.0 I had to use custom post_type and I needed relations
between them three times. Why? Because taxonomies are too basic: no
HTML description, no thumbnail, no ordering. How? Using post meta
because I didn't have the time to mess up writing a taxonomy mirroring
system.

I am totally sure that many developers will need such post2post
relations too. So I think adding a mirrored_tax_id to custom post
types and have WP handle the mirroring is a neat feature I'll use a
lot, and I am sure others will too.

Anyway you are pointing to some interesting feature/functions I wasn't
aware of. Let me ask you about them in a separate thread.

Regards

--
Lox
lox.dev@...
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[scribu]

I think I'm going to make my Posts 2 Posts plugin use a glue taxonomy, as
described by Daniel Cameron and Otto.

It should prevent the scaling issues described by Mike Schinkel.


On Tue, Aug 3, 2010 at 10:38 PM, Lox <lox.dev@...> wrote:

> 2010/8/4 Otto <otto@...>:
> > But what exactly are you mirroring? The title of the post? Some of the
> > content? A custom field?
>
> Eg: I have my movies and actors custom post_types. To make the
> relation I _mirrors_ actors (title + short description) to a custom
> taxonomy actors_tax and then apply the right tax term(s) to each
> movie.
>

With the approach I'm envisioning, the only thing mirrored will be the post
id.


--
http://scribu.net
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Lox-2]

2010/8/4 scribu <scribu@...>:
>> Eg: I have my movies and actors custom post_types. To make the
>> relation I _mirrors_ actors (title + short description) to a custom
>> taxonomy actors_tax and then apply the right tax term(s) to each
>> movie.
>>
>
> With the approach I'm envisioning, the only thing mirrored will be the post
> id.

Well having the post title mirrored is a must have. This allows ,for
example, to select and display a movie actors list _without_ querying
for the actor post... Saving database queries when possible is good.

--
Lox
lox.dev@...
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers