[wp-hackers] Taxonomy Schema Proposal

[Jamie Talbot]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jamie Talbot wrote:
> CREATE TABLE $wpdb->term_relationships (
> object_id bigint(20) NOT NULL default '0',
> term_taxonomy_id bigint(20) NOT NULL default '0'
> PRIMARY KEY  (object_ID)
> );
> 

While I'm thinking about this, is the object_id from this table expected to always come from
wp_posts?  Ryan mentioned this would also be for associating links.  If so, object_id can't be a
primary key as we'll be using ids from different tables.  Better perhaps to have a primary key
across the tuple, which will always be unique.  We'd also have to make sure we specified the
taxonomy in every query to make sure we didn't get back the wrong type inadvertently.

wp_links
1 http://wordpress.org WordPress ...
2 http://photomatt.net Matt ...

wp_posts
1 ... Hello World hello-world ...

SELECT tr.* FROM $wpdb->term_taxonomy AS t INNER JOIN $wpdb->term_relationships AS tr USING
term_taxonomy_id WHERE t.object_id = 1;

Could potentially return post_category, post_tag or link_category taxonomy data.  Specifying the
taxonomy will avoid this (assuming we always store the taxonomy correctly, and we've got bigger
problems if we don't...)

SELECT tr.* FROM $wpdb->term_taxonomy AS t INNER JOIN $wpdb->term_relationships AS tr USING
term_taxonomy_id WHERE t.object_id = 1 AND tr.taxonomy = 'post_category';

Would only return the post_category data, which you would assume would only ever be associated with
posts/pages.  Of course, if there is the possibility that we separate out wp_posts/wp_pages in the
future, we might have to revisit that.

Just throwing out some thoughts.

Cheers,

Jamie.

- --
http://jamietalbot.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGI1EzrovxfShShFARArEYAJwJQPyTmnp/zNfdBI6Nl+SFr5Sk/gCgiRtq
Ib74hUp51CwoGwqs4GP1jLs=
=gcN/
-----END PGP SIGNATURE-----