[wp-hackers] Taxonomy Schema Proposal

[Elias Torres]

I implemented tags on Apache Roller not long ago and we decided not to
create a terms table. We only have a table for entry2tag and a table for
aggregates (tag clouds).

create table roller_weblogentrytag (
    id              varchar(48)   not null primary key,
    entryid         varchar(48)   not null,
    websiteid       varchar(48)   not null,
    userid	    varchar(48)   not null,
    name            varchar(255)  not null,
    time            $db.TIMESTAMP_SQL_TYPE 	not null
);

create table roller_weblogentrytagagg (
    id              varchar(48)   not null primary key,
    websiteid       varchar(48) ,
    name            varchar(255)  not null,
    total           integer		  not null,
    lastused        $db.TIMESTAMP_SQL_TYPE 	not null
);

Basically, we store for every entry, one row per tag. This eliminates
most joins. Additionally, we have an aggregate table per website that
keeps track of the total number of times a tag is used. Remember Roller
is the equivalent of WP-MU, not WP.

Anyways, just a thought that joins might not be the best way to go.

-Elias

Mark Jaquith wrote:
> On Apr 18, 2007, at 5:04 PM, Matt Mullenweg wrote:
> 
>> Ultimately speed is going to be a big factor in any implementation, I
>> hope that the 3 additional tables and related queries don't hit us too
>> hard on the performance side.
> 
> If the API for updating/adding/removing tags and post categories is
> solid (i.e. we won't expect people to have a reason to circumvent it),
> we can cache cats/tags in postmeta whenever they are updated.  That
> would actually REMOVE a query from the existing system (as postmeta is
> already queried).  The system could be fail-safe, so that if there is no
> appropriate _wp_categories postmeta entry, it can do the big JOIN'd
> query (and at that moment, populate the postmeta cache).  That doesn't
> help us for Tag/Category views (which posts have X taxonomy?), but it
> helps us when querying tags/categories for a given set of posts.
> 
> I'd assume that would be faster than doing a three-way JOIN'd query...
> it certainly sped UTW up.
> 
> -- 
> Mark Jaquith
> http://markjaquith.com/
> 
> Covered Web Services
> http://coveredwebservices.com/
> 
> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>